Forum Discussion

SUMESHKUMAR22's avatar
SUMESHKUMAR22
Icon for Helper IV rankHelper IV
4 years ago
Solved

Calculation the previous value for % change based on date slicer selection

Hi Team,

In a page , I’m having a date range between slicer and showing card e.g. total sales which is simple sum aggregation. scenario needed : I want to create a %change card with indicator 🔺(green)🔻(red) which is possible BUT the problem statement is :- Example: When ever a user select any random date range slicer lets say 1Oct ,21- 10Oct,21 then the  previous value should calculate from 22Sep,21 - 1Oct,21 or let’s say if user selects 20 Oct - 25 Oct then the previous value should calculate from 15 Oct - 20 Oct so, just taking the previous for the same date range difference whatever user selects.
How to calculate the previous value in Dax for the % change calculation?

% change formula = (present- previous/ previous ) *100

Thank in advance

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi SUMESHKUMAR22 ,

     

    I have built a data sample

     

    Please try the following formula to create a measure:

    % change formula = 
var _curr=CALCULATE(SUM('Table'[Values]),DATESBETWEEN('Table'[Date],MIN('Table'[Date]),MAX('Table'[Date])))
var _diff=DATEDIFF(MIN('Table'[Date]),MAX('Table'[Date]),DAY)
var _pre=CALCULATE(SUM('Table'[Values]),  DATESBETWEEN('Table'[Date],MIN('Table'[Date])-_diff,MIN('Table'[Date])))
return  (_curr - _pre)/_pre

    Then format the measure:

     

    Output:

     

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

11 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi SUMESHKUMAR22 ,

     

    I have built a data sample

     

    Please try the following formula to create a measure:

    % change formula = 
var _curr=CALCULATE(SUM('Table'[Values]),DATESBETWEEN('Table'[Date],MIN('Table'[Date]),MAX('Table'[Date])))
var _diff=DATEDIFF(MIN('Table'[Date]),MAX('Table'[Date]),DAY)
var _pre=CALCULATE(SUM('Table'[Values]),  DATESBETWEEN('Table'[Date],MIN('Table'[Date])-_diff,MIN('Table'[Date])))
return  (_curr - _pre)/_pre

    Then format the measure:

     

    Output:

     

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

    • SUMESHKUMAR22's avatar
      SUMESHKUMAR22
      Icon for Helper IV rankHelper IV
      4 years ago

      Hi, 
      Thanks! Anonymous for the solution . I also approched the same method 🙂

    • SUMESHKUMAR22's avatar
      SUMESHKUMAR22
      Icon for Helper IV rankHelper IV
      4 years ago

      HI Anonymous ,

      In addition to your above dataset if we take another Column that is Category (A,B,C,D) then what changes to be done in the measure to find the % change according to the Category also (which will be a filter along with date ).
      Because there might be a situation where the  %change will be  0  for the ones where one of the category date started in between so then in that case %change will be blank / 0  for the previous value calculation.

      Please let us know how to update the measure for the same.

      Thanks in advance

      • SUMESHKUMAR22's avatar
        SUMESHKUMAR22
        Icon for Helper IV rankHelper IV
        4 years ago

        Hi Anonymous  & All,

        Please, let me know the updated Measure  for the additional scenario thats is mentioned above.

        Thanks in advance

    • SUMESHKUMAR22's avatar
      SUMESHKUMAR22
      Icon for Helper IV rankHelper IV
      4 years ago

      Hi ,

      Anonymous , thank you 🙂

      Also, wanted to check that if I create one measure for _curr & another measure for % change _pre. & Now if i use the Sum of value as a direct reference inside the calculate function for % change _pre then the % change logic will give incorrect answer. ( becuase the explicit date filter is applied in the report).

      So, how can we override that filter condition thats is automatically getting applying in that reference measure ( that is _curr)?

      I tried to use all function but its not working. Also I have one fact table and the  date is mapped with the date dimension ( one- many ).

  • VijayP's avatar
    VijayP
    Icon for Community Champion rankCommunity Champion
    4 years ago

    SUMESHKUMAR22 

    What is the logic
    when a user selecte same start and end date , how power bi need to understand to select a previous month date?!