Forum Discussion
MAT for custom time format
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"
)- Got rid of the countrows to check for enough data, have that # available via the Idex column. I used max in order to make the grand totals work as well
- Want to use ALLEXCEPT instead of ALL. This tells dax to ignore all the filteres except the one placed on product
I think that is what may have had in mind?
Nick_M, I was a bit in a hurry with positive conslutions :smileysad: 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"
)
- Got rid of the countrows to check for enough data, have that # available via the Idex column. I used max in order to make the grand totals work as well
- Want to use ALLEXCEPT instead of ALL. This tells dax to ignore all the filteres except the one placed on product
I think that is what may have had in mind?