Dynamic Yearly Date Ranges based on a Max Date (2)

[garynorcrossmmc]

Hi,

A user has requested that 3 date ranges be built into a Power BI report as a slicer - 12 Months, 24 Months and 36 Months based on the maximum date from another slicer.  The date ranges should look back 12/24/36 months before the maximum date.  How can I build this onto my date table?  I've tried 

Lookback Period =

IF(

FILTER(

'HR DIM_MONTH',

'HR DIM_MONTH'[Date (End of Month)]>=MAX('HR DIM_MONTH'[Date (End of Month)])-365 &&

'HR DIM_MONTH'[Date (End of Month)]<=MAX('HR DIM_MONTH'[Date (End of Month)])

),

"12 Months",

IF(

FILTER(

'HR DIM_MONTH',

'HR DIM_MONTH'[Date (End of Month)]>=MAX('HR DIM_MONTH'[Date (End of Month)])-730 &&

'HR DIM_MONTH'[Date (End of Month)]<=MAX('HR DIM_MONTH'[Date (End of Month)])

),

"24 Months",

IF(

FILTER(

'HR DIM_MONTH',

'HR DIM_MONTH'[Date (End of Month)]>=MAX('HR DIM_MONTH'[Date (End of Month)])-1095 &&

'HR DIM_MONTH'[Date (End of Month)]<=MAX('HR DIM_MONTH'[Date (End of Month)])

),

"36 Months",

0

)))

But this returned an error.  How can I achieve this?  

[v-deddai1-msft · Accepted solution]

Hi @garynorcrossmmc ,

If you want to create date range based on the maximum date from another slicer.  Calculated column is not an approach to you.

You can create a unassociated table for slicer:

Then use the following measure in your report:

Measure1 = SWITCH(SELECTEDVALUE('Table[RANGE]'),"-12Months",CALCULATE([Your Measure],DATEADD('HR DIM_MONTH'[Date (End of Month)],-1,YEAR)),"-24Months",CALCULATE([Your Measure],DATEADD('HR DIM_MONTH'[Date (End of Month)],-2,YEAR)),"-36Months",CALCULATE([Your Measure],DATEADD('HR DIM_MONTH'[Date (End of Month)],-3,YEAR)),[Your Measure])

Or you can also use calculation group in your data model, please refer to https://www.sqlbi.com/articles/introducing-calculation-groups/

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai