Dynamic Yearly Date Ranges based on a Max Date

[garynorcrossmmc]

Hi all,

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?  

[Anonymous · Accepted solution]

Hi @garynorcrossmmc ，

Please correct me if I wrongly understood your question.

I create three measures by DATESINPERIOD DAX to return the values from last 12 months ,last 24 months and last 36 months .You can put the measures in report to filter the datas meet date condition but not as a slicer.

Last 12 months = CALCULATE(SUM('Table'[ID]),DATESINPERIOD('Calendar Date'[Date],MAX('Calendar Date'[Date]),-12,MONTH ))

Last 24 months = CALCULATE(SUM('Table'[ID]),DATESINPERIOD('Calendar Date'[Date],MAX('Calendar Date'[Date]),-24,MONTH ))

Last 36 months = CALCULATE(SUM('Table'[ID]),DATESINPERIOD('Calendar Date'[Date],MAX('Calendar Date'[Date]),-36,MONTH ))

Otherwise, I create a one to many relationship between data table and calendar table.

Best Regards

Community Support Team _ Ailsa Tao

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

[lbendlin]

This only seems to produce the label for the selected period, not the actual filters.  Remove the ",0"  at the end to get rid of the error. Or - describe again what you are planning to achieve.