Forum Discussion

Nicks612's avatar
Nicks612
Helper I
1 year ago
Solved

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

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 ...
Need Help
  • speedramps's avatar
    speedramps
    1 year ago

    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

     

     

Nasif_Azam's avatar
Nasif_Azam
Super User
1 year ago

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