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

Nicks612's avatar
Nicks612
Helper I
1 year ago

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!