Forum Discussion
How to calculate difference between data of two selected dates.
Super UserHey 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
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).
this really works. thanks!!
