Forum Discussion

icassiem's avatar
icassiem
Post Prodigy
2 years ago
Solved

How to dynamically calculate the previous range based off current date selection

Hi, Please Help

 

i have a scnario where i need to calculate an indicator on Measure revenue

1. If date picker range is a week/month/ytd selected, get preious week/month/ytd range > its not a parallel period "year minus 1"

2. can i get the datepicker to be show in 4 formats (week/month/ytd/custom) so that i can get the previous range

3. if its custom date, im not sure how to select the previous days"

 

Is it something where one counts the selected dates and do a minus the day, issue is the mont/ytd wont hvae the same days going back and i dont have prefitlered selection to say its weeks/month/ytd/custom

 

Please any ideas, provide calculation examples or links

 

Regards

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi, icassiem 

     

    To dynamically calculate previous periods, not just parallel periods such as Year Minus 1, you can leverage DAX functions in Power BI. The key here is to understand the context of the selected date range and then apply logic to calculate the previous range accordingly.

     

    For week/month/year-to-date calculations, you can use a combination of DAX functions such as , , and . However, since your requirements are more than just parallel cycles, it will be especially useful.

    Example of the calculation for the previous month:

     

    Previous Month Revenue =
CALCULATE(
[Revenue1],
DATEADD('DateTable'[Date], -1, MONTH)
)


    This example assumes that you have a date table in your model. Adjust in to weeks or for YTD calculations and adjust accordingly.

     

    For more information on creating date tables and using DAX for time intelligence:

     

    Create date tables in Power BI Desktop - Power BI | Microsoft Learn

     

    Time intelligence functions (DAX) - DAX | Microsoft Learn

     

    Power BI currently does not natively support a single DatePicker that dynamically switches formats (week/month/YTD/custom). However, you can create a slicer with relative date filtering to achieve a similar outcome for week, month, and YTD. You can check the following link:

     

    Create a relative date slicer or filter in Power BI - Power BI | Microsoft Learn

     

    For custom date ranges and calculating the previous range, the method really depends on the logic you want to apply.

     

    How to Get Your Question Answered Quickly 

    Best Regards

    Yongkang Hua

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

     

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, icassiem 

     

    To dynamically calculate previous periods, not just parallel periods such as Year Minus 1, you can leverage DAX functions in Power BI. The key here is to understand the context of the selected date range and then apply logic to calculate the previous range accordingly.

     

    For week/month/year-to-date calculations, you can use a combination of DAX functions such as , , and . However, since your requirements are more than just parallel cycles, it will be especially useful.

    Example of the calculation for the previous month:

     

    Previous Month Revenue =
CALCULATE(
[Revenue1],
DATEADD('DateTable'[Date], -1, MONTH)
)


    This example assumes that you have a date table in your model. Adjust in to weeks or for YTD calculations and adjust accordingly.

     

    For more information on creating date tables and using DAX for time intelligence:

     

    Create date tables in Power BI Desktop - Power BI | Microsoft Learn

     

    Time intelligence functions (DAX) - DAX | Microsoft Learn

     

    Power BI currently does not natively support a single DatePicker that dynamically switches formats (week/month/YTD/custom). However, you can create a slicer with relative date filtering to achieve a similar outcome for week, month, and YTD. You can check the following link:

     

    Create a relative date slicer or filter in Power BI - Power BI | Microsoft Learn

     

    For custom date ranges and calculating the previous range, the method really depends on the logic you want to apply.

     

    How to Get Your Question Answered Quickly 

    Best Regards

    Yongkang Hua

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

     

     

    • icassiem's avatar
      icassiem
      Post Prodigy
      2 years ago

      Good day,

      Apologies for the late reply, Thank You for Helping Anonymous 
      I have setup a datatable and been trying to check the dates returned for parallel & date add 

      RevenueComparisonDateRange =
      --CALCULATE(min(Calc_Calendar[Date]),parallelperiod(Calc_Calendar[Date], -1, MONTH))
      CALCULATE(min(Calc_SpecialDates[Date]),dateadd(Calc_SpecialDates[Date], -1, year))
       
      1. My thinking is to perhaps check if the date picker filters equals the YTD values then calculate using the Parallel/sametimelastyear functions and if not calculate the datepicker month diff between the date picker selections and do dateadd month subtract. Please assist with calculating the date ranges for date month count between dates and dateadd/sametimelastyear ?
       
      2. Date Picker to be on a year/month grain only as the slicer is filtered on YTD and revenue is on month level
       
      Please advise?
      Regards