Power BI Desktop - Calculate datediff with information from same column

[datanau001]

Hello all,
I want to make the datediff with the information from column “change_date_time”.
In this column, for each “pair” of lines, I have the time stamp for status queued and time stamp for status accepted.
But here I’m not sure about the approach to make this calculation since the information is in lines and not in columns. See below:

I tried with duplicating the table and then work with filters and a new measure, however, it does not seem to be a good approach in my case.
Which would be your suggestion? I mean, to handle this calculation in just one table.
Regards
Marcelo

[CNENFRNL]

Hi, @datanau001 , 

IMHP, there's no point duplicating the dataset for such a calculation. In-line filters can easily do the trick, for example, for a certain SR_NUMBER,

Diff =
CALCULATE ( MAX ( SYSLOG[CHANGE_DATE_TIME] ), SYSLOG[DWH_STATUS] = "Accepted" )
    - CALCULATE ( MAX ( SYSLOG[CHANGE_DATE_TIME] ), SYSLOG[DWH_STATUS] = "Queued" )

[Fowmy · Accepted solution]

@datanau001 

You can Pivot the Status Column on the Change Date Time field then add a new field to calculate the difference.

Select the Status Column, under Transform Tab, click Pivot Column, 
Under the Value column, Select Change-Date-Time column, Select No Aggregation under Advanced Option.
now you get two columns ACCEPTED and  QUEUED. select those columns, under Add Columns Tab, select  Time and Subtract.
You will get a new column with the time difference.

If you find any difficulties, post a sample data with your reply to demonstrate.

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[datanau001]

@Fowmy, exaclty what I was looking for.

Thank you!

Marcelo