Forum Discussion

Uzi2019's avatar
Uzi2019
Icon for Community Champion rankCommunity Champion
3 years ago
Solved

Dynamic Comparison of Date range

Hello Expert,
I want to create a measure that compare dynamic date range . For example:

Scenario 1: if user selects randomly 14th - 16th sept. So it should calculate data from 7th - 9th sept of last week. like this in given image.

Scenario 2: If user select 1st - 30th sept , It should calculate previous month 1st - 31th Aug.


Please help me to find dynamic solution to this problem.

Thanks you in advance. 

Need Help
  • Tahreem24's avatar
    Tahreem24
    3 years ago

    Uzi2019 Try this DAX:
    First create a measure to get the no. of days between date range:

    No.ofDay = DATEDIFF(MIN(Date),MAX(Date),DAY)+1

    Then create  a measure which will get the sales value of prev selected days:

    Prev Days Sales = CALCULATE([Sales], DATEADD(Date[Date], - [No.ofDay], DAY)

4 Replies

  • MAwwad's avatar
    MAwwad
    Icon for Solution Sage rankSolution Sage
    3 years ago

     

    For Scenario 1, you can use the following measure in DAX:

     

     
    Measure = CALCULATE(SUM(Table[Value]), DATESBETWEEN(Table[Date], SELECTEDVALUE(Table1[Start Date]) - 7, SELECTEDVALUE(Table1[End Date]) - 7 ) )

    This will calculate the sum of "Value" for the selected date range (14th-16th Sept) by using DATESBETWEEN function with a date range starting 7 days before the selected start date and ending 7 days before the selected end date.

    For Scenario 2, you can use the following measure in DAX:

     

     
    Measure = CALCULATE(SUM(Table[Value]), DATESBETWEEN(Table[Date], EOMONTH(SELECTEDVALUE(Table1[Start Date]),-1)+1, EOMONTH(SELECTEDVALUE(Table1[Start Date]),-1)+DAY(EOMONTH(SELECTEDVALUE(Table1[Start Date]),-1)) ) )
     

    This will calculate the sum of "Value" for the selected date range (1st-30th Sept) by using DATESBETWEEN function with a date range starting from the first day of the previous month and ending on the last day of the previous month.

    Note: In both scenarios, replace "Table" with the name of your table and "Value" with the name of your column that you want to aggregate.

    • Uzi2019's avatar
      Uzi2019
      Icon for Community Champion rankCommunity Champion
      3 years ago

      This solution would not work for me.
      I need one measure which dynamically calculate same previous period as per date range selected by user.
      Date range can be 7 days , 3 days, 21 days or 1 month etc in same date range filter. So previous period measure should calculate as per date selection made by user.

      • Tahreem24's avatar
        Tahreem24
        Icon for Super User rankSuper User
        3 years ago

        Uzi2019 Try this DAX:
        First create a measure to get the no. of days between date range:

        No.ofDay = DATEDIFF(MIN(Date),MAX(Date),DAY)+1

        Then create  a measure which will get the sales value of prev selected days:

        Prev Days Sales = CALCULATE([Sales], DATEADD(Date[Date], - [No.ofDay], DAY)