Forum Discussion
Dynamic Yearly Date Ranges based on a Max Date
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 ))) |
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.
2 Replies
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.
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.
