Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
I have my own custom time frame where every year is splitted in 13 4-week time periods.
Year | 4-week time period |
2018 | 1 |
2018 | 2 |
2018 | 3 |
2018 | 4 |
2018 | 5 |
2018 | 6 |
2018 | 7 |
2018 | 8 |
2018 | 9 |
2018 | 10 |
2018 | 11 |
2018 | 12 |
2018 | 13 |
I have 2 tasks based on it:
- I would like to make 'MAT' measure, which will calculate moving averages based on this data
(e.g. MAT for 2018'7 is equal to 2017'8+2017'9+2017'10+...+2018'6+2018'7).
- To calculate 'MAT growth'
(e.g. 'MAT growth' for 2018'7 is equal to 'MAT' for 2018'7 divided by 'MAT' for 2017'7)
Hope, someone has come across such a case and solved it 🙂 Thanks
Solved! Go to Solution.
No worries, there is always a way!
Back in our table, I added a "Product" column with "Apple, Banana, Pear". Now, what we need to do is modify that "Index" column not to just look at the Year4WkID, but also the Product. Our new code:
Index = Var CurrentYrWk= MAT[Year4wkID] VAR CurrentProduct= MAT[Product] RETURN CALCULATE( COUNTROWS( FILTER( ALL ( 'MAT'), MAT[Year4wkID] <= CurrentYrWk && MAT[Product] = CurrentProduct ) ) )
This will give us the count of rows of all the rows that that are less than or equal to the current row's Year4WkId AND where the currrent row's Product is the same:
Need to modify the MAT code as well:
MAT =
IF( MAX( MAT[Index]) >=12,
AVERAGEX(
FILTER(
ALLEXCEPT(MAT,MAT[Product]),
MAT[Index] <= MAX(MAT[Index])
&& MAT[Index] >= MAX(MAT[Index])-11
),
[MAT Sales]
),
"Not Enough Data"
)
I think that is what may have had in mind?
Hi @lingvistt,
Could you please mark the proper answers as solutions?
Best Regards,
Dale
See if this helps:
Year4wkID = 'MAT'[Year]*100+MAT[4-week time period]
2. Using that column, create an Index where we can "go back in time". This will count how many rows are less than or equal to the current row:
Index = Var CurrentYrWk= MAT[Year4wkID] return CALCULATE( COUNTROWS( FILTER( ALL ( 'MAT'), MAT[Year4wkID] <= CurrentYrWk ) ) )
Now we have the data in the table that we can use to write a measure:
here's the basic measure ( i added in "Sales" to be able to work with):
MAT Sales = SUM ( MAT[Sales]) MAT = AVERAGEX( FILTER( ALL(MAT), MAT[Index] <= MAX(MAT[Index]) && MAT[Index] >= MAX(MAT[Index])-11 ), [MAT Sales] )
Though, probably want to check to see if there is enough data for an average. So added in a countrows to check to be sure there is enough data:
MAT = IF( CALCULATE( COUNTROWS( FILTER( ALL(MAT), AT[Index] <= MAX(MAT[Index]) && MAT[Index] >= MAX(MAT[Index])-11 ) ) )>=12, AVERAGEX( FILTER( ALL(MAT), MAT[Index] <= MAX(MAT[Index]) && MAT[Index] >= MAX(MAT[Index])-11 ), [MAT Sales] ), "Not Enough Data" /*Dont need anythign here, added this for illustration purposes*/ )
Final Table:
Was longer that I anticipated, but think a good start. (or at least I hope )
Hi Nick_M, Thanks for your intention to help and to the solution proposed. While trying to copy it I have come across a problem with the 2nd formula 🙂 Could you look at it please?
https://drive.google.com/open?id=1NIF36ICubEAaAJsMDPcqPiJ9VANudeBp
https://drive.google.com/open?id=1et5LqxwHL3f0Hqxap62Nr9VU7KcoZu5E
https://drive.google.com/open?id=1BlBol12o9YwsHQV7wTQ0NsTCRNTPrGaM
Looks like you are trying to use it as a Measure, it needs to be a Calculate Column in your table. Calculated Columns have row context, which is why you do not get the error, while measures need to have row context invoked by the "X" functions. So when you put that 2nd formula into a mesure DAX doesn't "know" what row(s) it should be looking at and errors out
Nick_M, I was a bit in a hurry with positive conslutions The thing is that the solution you propose works only for the cases when we have one only one row for year time frame (e.g. 2017 1 - only in one row).
When I try to apply your method to the dataset where all the data is recorded three times or more, it gives unclear results.
Example of the table:
(Year) (Period) (Sales Value) (Product)
2016 1 100.0 Apple
2016 2 200.0 Apple
...
...
...
2016 1 50.0 Orange
...
...
2016 1 30.0 Tomato
The result:
https://drive.google.com/open?id=1mkt5E-dceubSVMXmXCQyx7Lsa-RkESrr
Dataset example:
https://drive.google.com/open?id=1wzLIzXFyzWtDozLXGRi6kEPNlT9wB5nY
No worries, there is always a way!
Back in our table, I added a "Product" column with "Apple, Banana, Pear". Now, what we need to do is modify that "Index" column not to just look at the Year4WkID, but also the Product. Our new code:
Index = Var CurrentYrWk= MAT[Year4wkID] VAR CurrentProduct= MAT[Product] RETURN CALCULATE( COUNTROWS( FILTER( ALL ( 'MAT'), MAT[Year4wkID] <= CurrentYrWk && MAT[Product] = CurrentProduct ) ) )
This will give us the count of rows of all the rows that that are less than or equal to the current row's Year4WkId AND where the currrent row's Product is the same:
Need to modify the MAT code as well:
MAT =
IF( MAX( MAT[Index]) >=12,
AVERAGEX(
FILTER(
ALLEXCEPT(MAT,MAT[Product]),
MAT[Index] <= MAX(MAT[Index])
&& MAT[Index] >= MAX(MAT[Index])-11
),
[MAT Sales]
),
"Not Enough Data"
)
I think that is what may have had in mind?
Nick_M, thanks a lot 🙂 MAT works perfectly!
Do you have any ideas about the second part of the question about MAT growth:
I have 2 tasks based on it:
- I would like to make 'MAT' measure, which will calculate moving averages based on this data
(e.g. MAT for 2018'7 is equal to 2017'8+2017'9+2017'10+...+2018'6+2018'7).
- To calculate 'MAT growth'
(e.g. 'MAT growth' for 2018'7 is equal to 'MAT' for 2018'7 divided by 'MAT' for 2017'7)
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |