Forum Discussion
Power BI Desktop - Calculate datediff with information from same column
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
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 🙂
3 Replies
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 🙂
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" )
