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
Cmcmahan's avatar
Cmcmahan
Resident Rockstar
7 years ago

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

DebbieE's avatar
DebbieE
Community Champion
7 years ago

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

 

 

  • Cmcmahan's avatar
    Cmcmahan
    Resident Rockstar
    7 years ago

    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!