How to dynamically change 2 periods of comparison?

[Anonymous]

Hello,

Is there a smart way to change how I compare 2 periods of the same length?

Right now I have formulas that compare last 3 months vs last 3-6 months. To do this, I have just created a conditional column based on a relative month column (see first picture).

So I have these formulas to compare the periods:

Turnover L3M = CALCULATE(SUM('InitiativeMonitor_table'[Turnover (DKK)]),'InitiativeMonitor_table'[Period select2]="Period1")

Turnover L3-6M = CALCULATE(SUM('InitiativeMonitor_table'[Turnover (DKK)]),'InitiativeMonitor_table'[Period select2]="Period2")

Delta Turnover = [Turnover L3M]-[Turnover L3-6M]

etc. also with same formulas for Profit, Profit Margin % and Cost.

But if I would like to compare last 30 days vs last 30-60 days for example or last 6 months vs last 6-12 months or some other periods.Is there a smart way to accomplish this? Without editing the query and modifying or making new conditional columns.

 

[amitchandak · Accepted solution]

@Anonymous , Create rolling measures

Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,MONTH))

Rolling 3 before 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),3),-3,MONTH))

Rolling 30 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max('Date'[Date]),-30,Day))

Rolling 30 day before 30 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max('Date'[Date]) -30   ,-30,Day))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.