Forum Discussion
Calculating difference in historical data for multiple items
Hello,
I am struggling with some calculations in PowerBI:
I have a data table that contains about 100 vehicles with multipe columns and appropriate information.
This data table is a SharePoint-Online list. Since not only the actual data is relevant, but also the historical data to observe a develeopment is crucial, there is a PowerAutomate Flow running that saves the data table as a backup (csv) in a document library on SharePoint as well.
In PowerBI I want to visualize the development of different data for the vehicles. To this purpose, the different backup files are connected to PowerBI and merged to one data table (with different timestamps).
Now I want to calculate the difference between the timestamps to see the development (e.g. for the price development).
A section of the table could look like this:
| Number of Row | Vehicle | Timestamp | Price | Delta / Difference |
| 1 | 1 | 2023-02-20 | 2000 | ??? |
| 2 | 1 | 2023-02-10 | 1800 | ??? |
| 3 | 1 | 2023-02-01 | 1000 | ??? |
| 4 | 2 | 2023-02-20 | 20000 | ??? |
| 5 | 2 | 2023-02-10 | 18000 | ??? |
| 6 | 2 | 2023-02-01 | 17500 | ??? |
I am interested in calculating the difference to the last table entry:
So in the first row (2023-02-20, vehicle 1) the value in column "Delta / Difference" should be 200 (=2000-1800). The last row of the appropriate vehicle should have the value 0 in column "Delta / Difference" (here: row 3 and 6).
I am aware that the calculation itself is not rocket science, I just wonder how to implement the recognition of the "vehicle change".
Does anybody know a solution for this problem? Can it be done within PowerQuery or do I need Dax?
Many thanks in advance for any kind of help!
Hi Juli2102 ,
Here are the steps you can follow：
1. Create measure.
Measure = var _current= SUMX( FILTER(ALL('Table'),'Table'[Vehicle]=MAX('Table'[Vehicle])&&'Table'[Timestamp]=MAX('Table'[Timestamp])),[Price]) var _next= SUMX( FILTER(ALL('Table'), 'Table'[Vehicle]=MAX('Table'[Vehicle])&& 'Table'[Timestamp]= MAXX(FILTER(ALL('Table'),'Table'[Vehicle]=MAX('Table'[Vehicle])&&'Table'[Timestamp]<MAX('Table'[Timestamp])),[Timestamp])), [Price]) return IF( _next= BLANK(), 0, _current - _next)OR:
Measure 2 = var _current= SUMX( FILTER(ALL('Table'),'Table'[Vehicle]=MAX('Table'[Vehicle])&&'Table'[Number of Row]=MAX('Table'[Number of Row])),[Price]) var _next= SUMX( FILTER(ALL('Table'), 'Table'[Vehicle]=MAX('Table'[Vehicle])&& 'Table'[Number of Row]=MAX('Table'[Number of Row])+1), [Price]) return IF( _next= BLANK(), 0, _current - _next)2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
1 Reply
Hi Juli2102 ,
Here are the steps you can follow：
1. Create measure.
Measure = var _current= SUMX( FILTER(ALL('Table'),'Table'[Vehicle]=MAX('Table'[Vehicle])&&'Table'[Timestamp]=MAX('Table'[Timestamp])),[Price]) var _next= SUMX( FILTER(ALL('Table'), 'Table'[Vehicle]=MAX('Table'[Vehicle])&& 'Table'[Timestamp]= MAXX(FILTER(ALL('Table'),'Table'[Vehicle]=MAX('Table'[Vehicle])&&'Table'[Timestamp]<MAX('Table'[Timestamp])),[Timestamp])), [Price]) return IF( _next= BLANK(), 0, _current - _next)OR:
Measure 2 = var _current= SUMX( FILTER(ALL('Table'),'Table'[Vehicle]=MAX('Table'[Vehicle])&&'Table'[Number of Row]=MAX('Table'[Number of Row])),[Price]) var _next= SUMX( FILTER(ALL('Table'), 'Table'[Vehicle]=MAX('Table'[Vehicle])&& 'Table'[Number of Row]=MAX('Table'[Number of Row])+1), [Price]) return IF( _next= BLANK(), 0, _current - _next)2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly