Forum Discussion

garynorcrossmmc's avatar
garynorcrossmmc
Advocate IV
5 years ago
Solved

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

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 lo...
Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    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

v-deddai1-msft's avatar
v-deddai1-msft
Community Support
5 years ago

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