Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

How to calculate difference between data of two selected dates.

Dear Support Team,

I really need your help to solve my requirement. Thank you so much in advance. I have searched everywhere but not able to solve it.

My data source is as follows. Only one table, loaded from an excel (see below image).

I have to create 3 visuals (shown below) from the above data table.

  • Visual 1 shows the data for the first date only (chosen by date slicer 1).
  • Visual 2 shows the data for the second date only (chosen by date slicer 2).
  • Visual 3 shows the difference (Amount) calculated between two dates, by City and Customer. 

 Below is the complete report, I would like to implement. 

4 Replies

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    6 years ago

    Hey Anonymous ,

     

    this is not as simple as it may seem, this is because of the following.

    DAX does not provide any means to determine the source of the filtering happening to table. This means if there are to slicers with the same input, it may be possible to make the slicer act as they are independent by adjusting the interaction settings.

     

    But it's not possible to determine what date slicer has been used to filter the the table that contains the column, that is "feeding the slicer".

    One question, that is not answered, what happens if more than 2 dates are uses?

     

    I would write my measure like this. Assuming that VALUES(...[Date]) just contains 2 dates, I would use MAXX(VALUES(...) , '...'[date]) to determine one date and MINX to determine the 2nd date, and store these dates to 2 variables.

     

    Then I would use these variables to calculate 2 other varialbles like so:
    var value1 = CALCULATE(SUM('...'[Amount] , '...'[Date] = maxxdate)
    var value2 = CALCULATE(SUM('...'[Amount] , '...'[Date] = minxdate)

     

    The final result is than just the difference between value1 and value2.

     

    I guess it's possible, that the 3rd visual will show a result that you might not expect.

     

    Hopefully this provides you with some ideas.

     

    Regards,

    Tom

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi TomMartens , 

       

      Thank you for sharing your idea. However, It didn't help me to achieve the complete solution that I was looking for but using your idea, I am able to solve it differently and implement something similar. Here is how I did it.

      • Instead of using two date slicers, now I use only one slicer so I am able to catch the selected dates and use selected dates into a measure.
      • In order to make sure, not more than two dates get selected, I wrote logic to throw an error when selected more than two dates because the main purpose of this report to compare two dates data and calculate the difference amount.
      • Below are my Measure and reports (Output). 

       

    • spartanboy's avatar
      spartanboy
      Icon for Helper II rankHelper II
      4 years ago

      Hi Tom - well your solution was helpful for me but how to implement the same when the report is to be quarterly ?