Forum Discussion

lingvistt's avatar
lingvistt
Frequent Visitor
7 years ago
Solved

MAT for custom time format

Hi all,

 

I have my own custom time frame where every year is splitted in 13 4-week time periods.

 

Year4-week time period
20181
20182
20183
20184
20185
20186
20187
20188
20189
201810
201811
201812
201813

 

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 :smileyhappy:

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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?

     

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    See if this helps:

    1. Need to create a unique ID in your table.  

     

    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 :smileywink: )