Forum Discussion
Calculate Value Change / Value Difference between given Dates
Hello Community,
as I could not find any answer here or on other platforms, I would like to get your support with my first contribution:
I have a dataset of stock reports looking basically like this: UpdateDate, Type and Value.
For each ReportDate only Types with a Value are included. That means, if a Type is reduced to 0 between two reports, it will not be indicated, they just disappear. New Types can also show in newer reports without previous data.
| ReportDate | Type | Value |
| 5/10/2022 | 123 | 10 |
| 5/16/2022 | 123 | 10 |
| 5/23/2022 | 123 | 10 |
| 5/10/2022 | 345 | 20 |
| 5/10/2022 | 456 | 50 |
| 5/16/2022 | 456 | 50 |
| 5/23/2022 | 456 | 20 |
| 5/23/2022 | 567 | 15 |
| 5/16/2022 | 789 | 25 |
| 5/23/2022 | 789 | 30 |
I want to see the changes of the values. An example for the output would be (grouping by Type, for better understanding only)
| Type/CalendarDate | 5/10/2022 | 5/16/2022 | 5/23/2022 |
| 123 | +10 | 0 | 0 |
| 345 | +20 | -20 | 0 |
| 456 | +50 | 0 | -30 |
| 567 | 0 | 0 | +15 |
| 789 | 0 | +25 | +5 |
As the reports are not provided in a regular way, I created in the first step a CalendarTable and linked it to my dataset.
But then I am struggling with the start/end date to filter my calculation.
I guess I need variables indicating the last value, last date of the value and somehow an indicator that it is treated as zero, if there is no value for the type at a selected date.
Hope you have some suggestions.
Thank you!
Hi nexaframe ,
Please refer to my pbix file to see if it helps you.
Create a table.
Table 2 = VALUES('Table'[ReportDate])Then put the table 2 [reportdate] into the column filed.
Create two measures.
Measure = VAR _oreplrblank = CALCULATE ( MAX ( 'Table'[Value] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Type] IN VALUES ( 'Table'[Type] ) && 'Table'[ReportDate] IN VALUES ( 'Table 2'[ReportDate] ) ) ) VAR _harereplace = IF ( _oreplrblank = BLANK (), 0, _oreplrblank ) RETURN _harereplaceMeasure_2 = VAR _DATEBEFORE = CALCULATE ( MAX ( 'Table 2'[ReportDate] ), FILTER ( ALL ( 'Table 2' ), 'Table 2'[ReportDate] < MAX ( 'Table 2'[ReportDate] ) ) ) VAR _VALUEBEFORE = CALCULATE ( [Measure], FILTER ( ALL ( 'Table 2' ), 'Table 2'[ReportDate] = _DATEBEFORE ) ) RETURN [Measure] - _VALUEBEFOREBest Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
Hi nexaframe ,
Please refer to my pbix file to see if it helps you.
Create a table.
Table 2 = VALUES('Table'[ReportDate])Then put the table 2 [reportdate] into the column filed.
Create two measures.
Measure = VAR _oreplrblank = CALCULATE ( MAX ( 'Table'[Value] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Type] IN VALUES ( 'Table'[Type] ) && 'Table'[ReportDate] IN VALUES ( 'Table 2'[ReportDate] ) ) ) VAR _harereplace = IF ( _oreplrblank = BLANK (), 0, _oreplrblank ) RETURN _harereplaceMeasure_2 = VAR _DATEBEFORE = CALCULATE ( MAX ( 'Table 2'[ReportDate] ), FILTER ( ALL ( 'Table 2' ), 'Table 2'[ReportDate] < MAX ( 'Table 2'[ReportDate] ) ) ) VAR _VALUEBEFORE = CALCULATE ( [Measure], FILTER ( ALL ( 'Table 2' ), 'Table 2'[ReportDate] = _DATEBEFORE ) ) RETURN [Measure] - _VALUEBEFOREBest Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello Anonymous
Thanks for your support.
Somehow it works in your attached file, but when I copy the exact same solution to the test data, it doesn't?
Unfortunately I can not upload the file here?
Also seeing your solution: The "total" calculation is not working by type and also not by date?
Any help is really appreciated.
Thank you!
Hi nexaframe ,
It works well. Could you please provide your pbix file without privacy information for me to study?
"The "total" calculation is not working."
Please refer to it.
Or create a measure base on measure_2.
Measure_3 = VAR _a = [Measure_2] VAR _b = SUMMARIZE ( 'Table', 'Table'[Type], "aaa", [Measure_2] ) RETURN IF ( ISINSCOPE ( 'Table'[Type] ), _a, SUMX ( _b, [aaa] ) )Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi nexaframe ,
We have create a table2 before.
Then put the table 2 [reportdate] into the column filed in the matrix!
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
nexaframe , Try a new column like
Diff with last date =
var _1 = maxx(filter(Table, Table[Type] = earlier(Table[Type]) && [ReportDate] < earlier([ReportDate])),[ReportDate])
return
[Value] - maxx(filter(Table, Table[Type] = earlier(Table[Type]) && [ReportDate] < earlier([ReportDate])),[Value])amitchandakthanks for your super fast reply. Unfortunately it works only partially.
- It works if there is data for a type on all reportdates and it works if a new type is popping up on a later report date and not from the beginning. BUT: It doesn't work if a type just disappears.
In my example above, the issue is with Type "345", it shows "20 on the first date, but than empty columns for the rest. Expected would be "-20" on the next date, then later 0.
Do you have any suggestions?
Thank you!
