Forum Discussion
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 suggestions would be appreciated
Debbie
- 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
5 Replies
So you're trying to add a calculated column that indicates the previous 3 months? So for any date in June, it should indicate March, April, and May of that year, and in December it should indicate September, October, and November of that year?
You could set up the calculated column like so:RollingQuarterStart = DATEADD( STARTOFMONTH(dimDate[Date]), -3, month)
RollingQuarterEnd = DATEADD( STARTOFMONTH(dimDate[Date]), -1, day)
And then any time you need to reference the Rolling Quarter, filter for dates between RollingQuarterStart and RollingQuarterEnd
Unfortunately neither of these solutions are whay I need. I already have a date time dimension.
I need a flag against the dates in the date time dimension to denote the current quarter, so We are in July, the flag will be set against March April and May
I'm not sure why you're saying my solution wouldn't work. However you go about this, you should add this data as a column to your existing date dimension, since the flag is a property of specific dates.
I originally set it up so that you could pick any date and determine what that date's rolling quarter is. If you only ever want a TRUE/FALSE flag of whether something is in the Rolling Quarter based on the current date, you can adapt the DAX from above like so:RollingQtrFlag = dimDate[Date] >= DATEADD(DATE(YEAR(TODAY()), MONTH(TODAY()), 1), -3, MONTH) && dimDate[Date] < DATE(YEAR(TODAY()), MONTH(TODAY()), 1))
Note that since we're not dealing with a column anymore, I had to calculate the first of the month manually. This could also be implemented as a measure instead of a calculated column if that suits your needs better.
At this point, you can filter/calculate like so:
TestSum = CALCULATE(SUM(Data[Amount]), FILTER(ALL(dimDate), dimDate[RollingQtrFlag] = TRUE()))
EDIT: ahhh, you ninja'd me by 3 minutes. Good job figuring it out!
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
- 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
