March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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 ))) |
Solved! Go to 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.
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.
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
121 | |
98 | |
89 | |
72 | |
62 |
User | Count |
---|---|
140 | |
121 | |
106 | |
98 | |
94 |