Forum Discussion

DebbieE's avatar
DebbieE
Community Champion
7 years ago
Solved

Create a Rolling Current Quarter

Im trying to figure out how to add a rolling current quarter flag into my date dimension   So we are now in June so it should be March April and May (take the last month as the start)   Any sugge...
Need Help
  • DebbieE's avatar
    DebbieE
    7 years ago
    Rolling Quarter Flag = IF(DATEDIFF('dim Date'[date].[Date],TODAY(),MONTH)<=3 && DATEDIFF('dim Date'[date].[Date],TODAY(),MONTH)>0,1,0)
     
    This seems to do the trick
C-G-Davidson's avatar
C-G-Davidson
Frequent Visitor
7 years ago

Hi DebbieE ,

 

My advice would be to create a month offset column in your table. The following is M code from Avi Singh's Ultimate Calendar. The dimension is called Calendar and he creates it using :

 

 ( Date.Year([Date]) - Date.Year(CurrentDate) ) * 12
+ Date.Month([Date]) - Date.Month(CurrentDate)

 

From there you could create a quarter average by filtering to all data where currentMonthOffset is between -1 and -4 and then if you wanted you can change it to -1 and -7 to get 6 months etc.

 

Hopefully that helps.

 

Colin