Forum Discussion

garynorcrossmmc's avatar
garynorcrossmmc
Advocate IV
5 years ago
Solved

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 shoul...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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.