Need help for generating raw wise difference from column using DAX or power query

Here is M-Code that will calculate the Difference columns, and arrange it in the order you show.

Paste the code below into the Advanced Editor to see how it works, then adapt it to your actual data.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdBBCgIxDAXQq0jXIzRJk5+ew+UwOxHduNC5P5bWkakd6CLQx+cn8xz0zOVF1jAFeq+ny/1xW8vMlhJpLpNld1BYpoptw/y87jGcvOJM6gOW1x4rCdAwp7xhHCcrXLVhGAbcJxtpxYgx8q+GHy9oYoKGiWVIHjC+WMr3f3Lf2eDiDaukAfedQUax4XLEDefjGhCoNeylz7J8AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Shift = _t, #"Machine A" = _t, #"Machine B" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Date", type date}, {"Shift", type text}, {"Machine A", Int64.Type}, {"Machine B", Int64.Type}}),
    
    #"Add Differences" = 
        [a=List.Skip(#"Changed Type"[Machine A]),
         b=List.Skip(#"Changed Type"[Machine B]),
         c=List.Zip({a,#"Changed Type"[Machine A]}),
         d=List.Zip({b,#"Changed Type"[Machine B]}),
         e=List.Transform(c, each _{0} - _{1}),
         f=List.Transform(d, each _{0} - _{1}),
         g=Table.FromColumns(
             Table.ToColumns(#"Changed Type")
             & {e,f},
             type table[Date=date, Shift=text,Machine A=Int64.Type,Machine B=Int64.Type,
                        Difference A=Int64.Type, Difference B=Int64.Type])][g],

    #"Reordered Columns" = Table.ReorderColumns(#"Add Differences",
        {"Date", "Shift", "Machine A", "Difference A", "Machine B", "Difference B"})
        
in
    #"Reordered Columns"

Results from your data:

Just by formatting the Line Chart visual, you can get this:

I don't know how to rotate the x-axis labels on this visual except by making the visual more narrow. I merely used Data and Shift for the x-axis and selected to concatenate the labels.

There are lots of ways to solve this problem but try this ...

Click here to download a solution from Onedrive

Click here 

How it works ...

Change bakue data types to numeric
Change the date to a proper date.

Sort by date and shift.

Add Index colum

Use the index to get the previous meter reading for A.
Then use the index to get the previous meter reading for B.
 

Calculate the usage

Draw the graph

Please click thumbs up because I have tried to help.

Then click [accept solution] if it works.

Many thanks !

Hey Nicks612 ,

To calculate the difference in readings (Machine A and Machine B) shift-wise in Power BI using DAX and Power Query, here's a detailed breakdown for both approaches.

Power Query Steps

You can sort the data and use the Index method to calculate the difference:

1. Sort the table:

   • Sort by Date (ascending), then Shift (custom order: 1st Shift, 2nd Shift, 3rd Shift).

2. Add Index Column:

   • Add an Index column starting from 0.

3. Duplicate the table:

   • Right-click the query and Duplicate.

4. Rename duplicated columns:

   • Rename Machine A to Prev Machine A, Machine B to Prev Machine B, and Index to Prev Index.

5. Merge Queries:

   • Merge the original table with the duplicated one using:

     • Index = Prev Index + 1 (join on: Index from original and Prev Index from duplicate).

6. Expand columns:

   • Expand Prev Machine A and Prev Machine B.

7. Add Difference columns:

   Difference A = [Machine A] - [Prev Machine A]
   Difference B = [Machine B] - [Prev Machine B]

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

In the attached workbook we have:

• Your source data top left
• A pivot created from a Power Query transform of your source data
• A chart of that pivot.

You will also notice next to your source data, a column E for a Machine C. This data isn't included in the pivot and chart because it's not currently part of your source data.

However, dragging the grab-handle bottom right of your data so that column E does become part of the source data table, then refreshing the pivot table yields the following:

Note:

I noticed what appears to be an anomaly with your source data where the dates/shifts seem to be out of order:

The 1st shift on the 27th May seems to take place after the 1st Shift on the 28th May. Consequently, there's a step in my query which sorts on the stroks count which really shouldn't be there.

Hi Nicks612 ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you speedramps Nasif_Azam ronrsnfld p45cal for the prompt response.

I wanted to check if you had the opportunity to review the information provided.If the response has addressed your query, please accept it as a solution so other members can easily find it.

Thank you.

In the above example I assumed that shift #2 comes after shift #1, but this creates negative usage on the 27/05/2025

In this method I have sorted by machine and reading number 

Click here to download from Onedrive

Click here 

I dont know what sequence you require, but I trust I have taught you the method well enough now
and that you can change the sort sequence as you require

Hello everyone Nasif_Azam , speedramps , ronrsnfld , p45cal & v-venuppu ,

Thank you so much for the different views and ideas to solve this issue... your efforts are really appreciable...

Thanks a lot!