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

[Nicks612]

Hello everyone,

Can anyone help me with the below problem?

I have attached table named "Raw Data" which contains the Date, Shift, Machine A & Machine B columns. Machine A & Machine B columns has reading of perticular machine stroks that machine has done in entire shift which is always in increasing manner.

I want to calculate the difference between two reading of machine A as shown in the table named "Resultant Data". (similar, for the machine B also). then, want two plot a line chart indicating difference A & Difference B with respect to date & shift. 

Raw Data:

Date	Shift	Machine A	Machine B
5-25-2025	1st Shift	2644159	698871
5-26-2025	2nd Shift	2647818	699158
5-26-2025	3rd Shift	2651377	699249
5-27-2025	2nd Shift	2657855	699767
5-27-2025	3rd Shift	2661555	700028
5-28-2025	1st Shift	2663637	700123
5-27-2025	1st Shift	2663677	700325
5-28-2025	2nd Shift	2667838	700534
5-28-2025	3rd Shift	2671610	700781
5-29-2025	1st Shift	2673756	700877

Resultant Data:

Date	Shift	Machine A	Difference A	Machine B	Difference B
5-25-2025	1st Shift	2644159	3659	698871	287
5-26-2025	2nd Shift	2647818	3559	699158	91
5-26-2025	3rd Shift	2651377	6478	699249	518
5-27-2025	2nd Shift	2657855	3700	699767	261
5-27-2025	3rd Shift	2661555	2082	700028	95
5-28-2025	1st Shift	2663637	40	700123	202
5-27-2025	1st Shift	2663677	4161	700325	209
5-28-2025	2nd Shift	2667838	3772	700534	247
5-28-2025	3rd Shift	2671610	2146	700781	96
5-29-2025	1st Shift	2673756	4431	700877	-700877

Note A: In resultant column, I have calculated the Difference A & Difference B columns via formula. Please suggest measure & calculated column (both ways) to derived the difference.

Note B: for date & shift hierarchy, should I have to go for manual hierarchy or we can add shift in default date hierarchy?

Please guide me on this issue. Let me know if you need any further clarification or information.

Thanks in Advance!

[speedramps]

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

[speedramps]

Hi again

Please try ask the right question first time, because it is a bit annoying having to answer twice 😀😀😀

Click here to download a solution ftom one drive

Click here 

This method will work for 25 machines or more

Please will you now click [thumbs up] and [accept solutoon button].   Thank you !

How it works ...

For the date correctly

Select the Date and Shift columns, then Transform> Unpiciot>Unpivot other columns

Sort by Machine, Date and  Shift

Add an index column from 0 incrementing by 1

Get the machine and reading for the previous row

Change reading to numeric data types
and add a conditional column

Renove the unneeded columns and change the usage ro a numeric data type

Draw your graph

[p45cal]

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.

[Nasif_Azam]

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

[Nicks612]

Hi @Nasif_Azam & @speedramps ,

Thank you for the prompt response. I have tried it and it worked well. Both of you have a similar kind of solution. May many thanks to both of you!

However, I want to know what if there are more than 25 columns and we need this difference for all these columns? It will be tedious to add formula and column for that type of data. So can you please suggest any alternate solution for multiple columns in terms of measure or something?

Thanks in Advance!

[ronrsnfld]

This modification of my algorithm shows a method of handling multiple columns without having to hard code them.

It uses your existing data sample, which shows that the columns to be processed are all of the columns except for the first two. 

It also uses a custom function to set the data types of the resultant table.

Again, paste the code into the Advanced Editor and read the comments to understand how it works

Main Code

let

//change next line to reflect actual data source
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdE9CsMwDAXgq5TMLViS9XeOjiFbKe3Soc39qbCSkOCABw3+ePLzOA58wzgFebgO8Jsv99f7OceMUiuwxyRuphCDFnfDYbo2JavCz2Ov1MCacmBrqqB4p+i7VwykmgqrpyIpq9LzLFZjTqWiqUytU8csAW4qLhfMDQFo29DO2xASWiIAKRVS7bI6pYsizFCotXZZx3eJGi3VcUQ0JSKdOr5LQaCkij9IZba14ecbKinLWl2uCu7RxvQH", 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, #"Machine C" = _t]),
    
//Written so as not to require listing all the column names
//Assumes Column one = Date; Column 2=Shift, and the rest are the columns to be processed
    colNames=Table.ColumnNames(Source),
    colTypes=List.Zip({List.Skip(colNames,2), List.Repeat({Int64.Type}, Table.ColumnCount(Source)-2)}),
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Date", type date}, {"Shift", type text}} & colTypes),

//In your sample data, the columns to process start with column three.
//this line may need to be changed if not the case in your actual data
    #"Cols to Process" = List.Skip(colNames,2),

//Create new column names by prefacing the processed columns with "Diff ".
//Other algorithms could be used
    #"New Col Names" = List.Transform(#"Cols to Process", each "Diff " & _),

//Create lists of differences for each processed column
    #"Compute Differences" = 
        List.Accumulate(
            #"Cols to Process",
            {},
            (s,cur)=> s & {[a=Table.Column(#"Changed Type",cur),
                         b=List.Skip(a),
                         c=List.Zip({a,b}),
                         d=List.Transform(c, each _{1} - _{0})
                         ][d]}),
    
//Create the "type table" line using a custom function
    #"Table Types" = [a=Table.ColumnNames(#"Changed Type"),
                      b=#"New Col Names",
                      c=a & b,
                      d={type date, type text} & 
                            List.Repeat({Int64.Type}, List.Count(#"Cols to Process")+ List.Count(#"New Col Names")),
                      e=fnRecordTypes(c,d)][e],

//Combine the existing and new columns
    #"Combine Columns" = Table.FromColumns(
                            List.FirstN(Table.ToColumns(#"Changed Type"),2+List.Count(#"Cols to Process"))
                            & #"Compute Differences", type table #"Table Types"),

//Order the columns as you show in your sample
    #"Reorder Cols" = Table.ReorderColumns(#"Combine Columns", 
        List.FirstN(Table.ColumnNames(#"Changed Type"), 2) & List.Combine(List.Zip({#"Cols to Process",#"New Col Names"})))
in
    #"Reorder Cols"

Custom function: Rename as per code comment

//Rename Query "fnRecordTypes"

(fieldNames as list, fieldTypes as list)=>
   
let
    rowColumnTypes = List.Transform(fieldTypes, (t) => [Type = t, Optional = false]),
    rowType = Type.ForRecord(Record.FromList(rowColumnTypes, fieldNames),false)
in
   rowType

This results in the same output as my previous answer. Note that if you create the chart in Power BI desktop, you can easily sort the X-axis labels , even though your data is not.

[Nasif_Azam]

Hey @Nicks612 ,

Glad to hear it worked! And that’s a great follow-up question regarding handling differences across many columns. If you’re dealing with 25+ columns, creating individual formulas for each can be tedious and error-prone. While DAX is powerful for dynamic measures, it isn’t ideal for row-wise comparisons across multiple columns. Instead, I’d suggest sticking with Power Query for scalability.

Power Query Pattern

You can automate the difference calculation across all relevant numeric columns using List.Transform and Record.TransformFields.

1. Sort your data by Date & Shift.

2. Add an Index Column (Index).

3. Duplicate the table and shift the index (PrevIndex = Index + 1).

4. Merge on Index and PrevIndex.

5. In the Merge step, after expanding the previous row's values:

   • Create a list of target columns (e.g., {"Machine A", "Machine B", ..., "Machine Z"})

   • Then dynamically compute differences:

let
    columnsToCompare = {"Machine A", "Machine B", "Machine C", ...},
    AddDifferences = Table.TransformColumns(
        MergedTable,
        List.Transform(columnsToCompare, each {
            "Diff " & _, 
            each Record.Field(_, _) - Record.Field(_, "Prev." & _), 
            type number
        })
    )
in
    AddDifferences

This will create Diff Machine A, Diff Machine B, etc. for all desired columns without hardcoding each formula.

 

When creating your line chart combine Date + Shift into a single column for a cleaner X-axis. Like:

[Date] & " - " & [Shift]

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

[Nicks612]

Hi @Nasif_Azam ,

Thank you so much for guidance and quick response. (Just for your information - I don't know much about M code so ignor silly mistakes I did when I followed these steps)

What I have done first is - merge two tables as a new and then expand the remaining columns as shown in below M code.

let
Source = Table.NestedJoin(#"Raw Data", {"Index"}, #"Raw Data Prev", {"Index"}, "Raw Data Prev", JoinKind.LeftOuter),
#"Expanded Raw Data Prev" = Table.ExpandTableColumn(Source, "Raw Data Prev", {"MAIN TR Power Reding ", "Bus Trunk -1 Meter reading", "Bus Trunk -6 Meter Reading", "Bus Trunk -2 Meter Reading", "Bus Trunk -4 Meter Reading", "Bus Trunk -3 Meter Reading", "6F2 Meter Reding", "Bus Trunk -5 Meter Reading", "Bus Trunk -9 Meter Reading", "Bus Trunk -10 Meter Reading", "Logistic Meter Reading", "Fan FDB Meter Reading", "Office + Canteen Meter Reading#(lf)", "ELDB Meter Reading", "Bus Trunk -7 Meter Reading", "8F4 Reading", "Main PDB Meter Reading", "U321 PDB Meter Reading (J3-601)", "PDB - 4 Meter Reading", "W601 Assembly Meter Reading"}, {"Raw Data Prev.MAIN TR Power Reding ", "Raw Data Prev.Bus Trunk -1 Meter reading", "Raw Data Prev.Bus Trunk -6 Meter Reading", "Raw Data Prev.Bus Trunk -2 Meter Reading", "Raw Data Prev.Bus Trunk -4 Meter Reading", "Raw Data Prev.Bus Trunk -3 Meter Reading", "Raw Data Prev.6F2 Meter Reding", "Raw Data Prev.Bus Trunk -5 Meter Reading", "Raw Data Prev.Bus Trunk -9 Meter Reading", "Raw Data Prev.Bus Trunk -10 Meter Reading", "Raw Data Prev.Logistic Meter Reading", "Raw Data Prev.Fan FDB Meter Reading", "Raw Data Prev.Office + Canteen Meter Reading#(lf)", "Raw Data Prev.ELDB Meter Reading", "Raw Data Prev.Bus Trunk -7 Meter Reading", "Raw Data Prev.8F4 Reading", "Raw Data Prev.Main PDB Meter Reading", "Raw Data Prev.U321 PDB Meter Reading (J3-601)", "Raw Data Prev.PDB - 4 Meter Reading", "Raw Data Prev.W601 Assembly Meter Reading"})
in
#"Expanded Raw Data Prev"

After that, I have replace the above code with your code as you suggested and end up with the following error - 

M code for this is - 

let
columnsToCompare = {{"MAIN TR Power Reding ", "Bus Trunk -1 Meter reading", "Bus Trunk -6 Meter Reading", "Bus Trunk -2 Meter Reading", "Bus Trunk -4 Meter Reading", "Bus Trunk -3 Meter Reading", "6F2 Meter Reding", "Bus Trunk -5 Meter Reading", "Bus Trunk -9 Meter Reading", "Bus Trunk -10 Meter Reading", "Logistic Meter Reading", "Fan FDB Meter Reading", "Office + Canteen Meter Reading#(lf)", "ELDB Meter Reading", "Bus Trunk -7 Meter Reading", "8F4 Reading", "Main PDB Meter Reading", "U321 PDB Meter Reading (J3-601)", "PDB - 4 Meter Reading", "W601 Assembly Meter Reading"}, {"Raw Data Prev.MAIN TR Power Reding ", "Raw Data Prev.Bus Trunk -1 Meter reading", "Raw Data Prev.Bus Trunk -6 Meter Reading", "Raw Data Prev.Bus Trunk -2 Meter Reading", "Raw Data Prev.Bus Trunk -4 Meter Reading", "Raw Data Prev.Bus Trunk -3 Meter Reading", "Raw Data Prev.6F2 Meter Reding", "Raw Data Prev.Bus Trunk -5 Meter Reading", "Raw Data Prev.Bus Trunk -9 Meter Reading", "Raw Data Prev.Bus Trunk -10 Meter Reading", "Raw Data Prev.Logistic Meter Reading", "Raw Data Prev.Fan FDB Meter Reading", "Raw Data Prev.Office + Canteen Meter Reading#(lf)", "Raw Data Prev.ELDB Meter Reading", "Raw Data Prev.Bus Trunk -7 Meter Reading", "Raw Data Prev.8F4 Reading", "Raw Data Prev.Main PDB Meter Reading", "Raw Data Prev.U321 PDB Meter Reading (J3-601)", "Raw Data Prev.PDB - 4 Meter Reading", "Raw Data Prev.W601 Assembly Meter Reading"}},
AddDifferences = Table.TransformColumns(
Merge1,
List.Transform(columnsToCompare, each {
"Diff " & _,
each Record.Field(_, _) - Record.Field(_, "Prev." & _),
type number
})
)
in
AddDifferences

Can you please explain why I'm getting this error and suggest a solution?

Thanks in Advance!

[Nasif_Azam]

Try this one:

let
// Step 1: Original & Previous table already merged
Source = Merge1, // your merged table

// Step 2: List of columns you want to compare
columnsToCompare = {
"MAIN TR Power Reding ", "Bus Trunk -1 Meter reading", "Bus Trunk -6 Meter Reading",
"Bus Trunk -2 Meter Reading", "Bus Trunk -4 Meter Reading", "Bus Trunk -3 Meter Reading",
"6F2 Meter Reding", "Bus Trunk -5 Meter Reading", "Bus Trunk -9 Meter Reading",
"Bus Trunk -10 Meter Reading", "Logistic Meter Reading", "Fan FDB Meter Reading",
"Office + Canteen Meter Reading#(lf)", "ELDB Meter Reading", "Bus Trunk -7 Meter Reading",
"8F4 Reading", "Main PDB Meter Reading", "U321 PDB Meter Reading (J3-601)",
"PDB - 4 Meter Reading", "W601 Assembly Meter Reading"
},

// Step 3: Add difference columns dynamically
AddDiffColumns = List.Accumulate(
columnsToCompare,
Source,
(state, colName) =>
Table.AddColumn(
state,
"Diff " & colName,
each try [colName] - Record.Field(_, "Raw Data Prev." & colName) otherwise null,
type number
)
)
in
AddDiffColumns

[speedramps]

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 !

[ronrsnfld]

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.