Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello dear PowerBI-Community
I hope you guys could help me with the following problem.
In the example (see table) there are several transports.
Each transport has an unique ID.
A transport can create 1 to n messages.
Actually my need is to measure the time interval to the next message on a transport.
For this reason i would like to create a calculated table in Power BI (see table - red column "timeGapToNextMessageOnTransportID")
transportID | messageCreated | timeGapToNextMessageOnTransportID |
1 | 09.12.2019 10:15 | 7.25 h |
1 | 09.12.2019 17:30 | x |
2 | 10.12.2019 11:45 | x |
3 | 12.12.2019 13:00 | 13.0 h |
3 | 13.12.2019 02:00 | 2.5 h |
3 | 13.12.2019 04:30 | 0.5 h |
3 | 13.12.2019 05:00 | x |
I you have any questions, please ask.
I would be very thankful if one of you guys could help me.
Have a great time!
Cheers.
qwertzuiop
Solved! Go to Solution.
Hi @qwertzuiop ,
Measure =
VAR temp_table =
FILTER (
SUMMARIZE (
ALL ( 'Table'[transportID]; 'Table'[messageCreated] );
'Table'[messageCreated];
'Table'[transportID]
);
'Table'[messageCreated] > MAX ( 'Table'[messageCreated] )
&& 'Table'[transportID] = SELECTEDVALUE ( 'Table'[transportID] )
)
VAR Current_Date =
SELECTEDVALUE ( 'Table'[messageCreated] )
VAR Time_difference =
DATEDIFF ( Current_Date; MINX( temp_table; 'Table'[messageCreated] ); MINUTE ) / 60 + 0
RETURN
Time_difference
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @qwertzuiop ,
Create the following measure:
Measure =
VAR temp_table =
FILTER (
SUMMARIZE (
ALL ( 'Table'[transportID]; 'Table'[messageCreated] );
'Table'[messageCreated];
'Table'[transportID]
);
'Table'[messageCreated] > MAX ( 'Table'[messageCreated] )
&& 'Table'[transportID] = SELECTEDVALUE ( 'Table'[transportID] )
)
VAR Current_Date =
SELECTEDVALUE ( 'Table'[messageCreated] )
VAR Time_difference =
DATEDIFF ( Current_Date; MAXX ( temp_table; 'Table'[messageCreated] ); MINUTE ) / 60 + 0
RETURN
IF ( Time_difference = 0; "-"; FORMAT ( Time_difference; ""##0.0# h" h" ) )
I'm assuming you don't want to have this calculated on total time level.
check PBIX file attach.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHello @MFelix
Thank you so much for you support and your well structured text and attached file.
It makes it very easy to follow your thoughts.
Nevertheless I have some questions:
figure 1
figure 2
Thank you so much for your help.
Your contribution is very valuable.
Cheers!
qwertzuiop
I don't know why but figure 2 was missing.
sorry
figrue 2
Excuse me
Now I got it.
The calculated table is the one out of my example.
But then why is this error?
Hi @qwertzuiop ,
My bad when I was testing out the information placed a MAXX when should be a MINX it's giving the difference to 5AM and not 2AM redo the measure to:
Measure =
VAR temp_table =
FILTER (
SUMMARIZE (
ALL ( 'Table'[transportID]; 'Table'[messageCreated] );
'Table'[messageCreated];
'Table'[transportID]
);
'Table'[messageCreated] > MAX ( 'Table'[messageCreated] )
&& 'Table'[transportID] = SELECTEDVALUE ( 'Table'[transportID] )
)
VAR Current_Date =
SELECTEDVALUE ( 'Table'[messageCreated] )
VAR Time_difference =
DATEDIFF ( Current_Date; MINX( temp_table; 'Table'[messageCreated] ); MINUTE ) / 60 + 0
RETURN
IF ( Time_difference = 0; "-"; FORMAT ( Time_difference; "##0.0# h" ) )
Corrected file attach.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHello @MFelix
Thank you so much for your quick answer and your great support.
The error is fixed.
But may I ask you some more things:
Thank you so much.
You're a great member of the Community 🙂
Cheers!
qwertzuiop
Hi @qwertzuiop ,
Measure =
VAR temp_table =
FILTER (
SUMMARIZE (
ALL ( 'Table'[transportID]; 'Table'[messageCreated] );
'Table'[messageCreated];
'Table'[transportID]
);
'Table'[messageCreated] > MAX ( 'Table'[messageCreated] )
&& 'Table'[transportID] = SELECTEDVALUE ( 'Table'[transportID] )
)
VAR Current_Date =
SELECTEDVALUE ( 'Table'[messageCreated] )
VAR Time_difference =
DATEDIFF ( Current_Date; MINX( temp_table; 'Table'[messageCreated] ); MINUTE ) / 60 + 0
RETURN
Time_difference
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |