Forum Discussion

spartan27244's avatar
spartan27244
Icon for Resolver I rankResolver I
5 years ago

Calculating Changes between user defined periods

I need to create the rate of change between the periods in a slicer, below is an example visual. You can see that the user has selected a range of report periods, and the dates in the grid are...
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

spartan27244 , usually in such cases when do have standard period we use rank on period

 

New column in date table

new column

Period Rank = RANKX(all('Period'),'Period'[year period],,ASC,Dense)

 

measure

This Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Period'),'Period'[Period Rank]=max('Period'[Period Rank])))
Last Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Period'),'Period'[Period Rank]=max('Period'[Period Rank])-1))

Last year same period= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Month] = Max('Date'[Month])))

 

or

 

Last Year Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Period'),'Period'[Period]=max('Period'[Period]) && 'Period'[Year]=max('Period'[Year])-1))

 

 

new column

Year Rank = RANKX(all('Date'),'Date'[Year Start date],,ASC,Dense)

 

new measures
This Year = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])))
Last Year = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])-1))

spartan27244's avatar
spartan27244
Icon for Resolver I rankResolver I
5 years ago

These are not standard Calendar year periods as indicated in the dates shown for illustrative purposes.  In addition the ReportPeriod table is a customer populationId, a Group Name (Calendar Year, Fiscal Year, Benefit Year, Custom), a period name 2015, 2016, 2017, etc. and a date field MedDate. There is a MedDate for every day within the period so that it redily joins with the model's DATE table.