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.
Hi
I need to create report where downtime duration will be splited by hour of day when it happened. Now I have those data:
start | end | duration[min] |
11:30 | 11:50 | 20 |
11:55 | 12:15 | 20 |
12:30 | 15:30 | 180 |
but need something like this:
start | end | duration[min] | hour of day | duration in hour[min] |
11:30 | 11:50 | 20 | 11 | 20 |
11:55 | 12:15 | 20 | 11 | 5 |
12 | 15 | |||
12:30 | 15:30 | 180 | 12 | 30 |
13 | 60 | |||
14 | 60 | |||
15 | 30 |
Many thanks
Michal
Solved! Go to Solution.
You may create tables to get it. Below is the sample file for your reference.
Table = GENERATESERIES(1,24,1)
Final = FILTER ( CROSSJOIN ( SampleData, 'Table' ), HOUR ( SampleData[start] ) <= 'Table'[Hour of Day] && HOUR ( SampleData[end] ) >= 'Table'[Hour of Day] )
Last, add the column with IF condition:
duration in hour = IF ( HOUR ( Final[start] ) = HOUR ( Final[end] ), MINUTE ( Final[end] ) - MINUTE ( Final[start] ), IF ( HOUR ( Final[start] ) = Final[Hour of Day], 60 - MINUTE ( Final[start] ), IF ( HOUR ( Final[end] ) = Final[Hour of Day], MINUTE ( Final[end] ), 60 ) ) )
Regards,
Cherie
You may create tables to get it. Below is the sample file for your reference.
Table = GENERATESERIES(1,24,1)
Final = FILTER ( CROSSJOIN ( SampleData, 'Table' ), HOUR ( SampleData[start] ) <= 'Table'[Hour of Day] && HOUR ( SampleData[end] ) >= 'Table'[Hour of Day] )
Last, add the column with IF condition:
duration in hour = IF ( HOUR ( Final[start] ) = HOUR ( Final[end] ), MINUTE ( Final[end] ) - MINUTE ( Final[start] ), IF ( HOUR ( Final[start] ) = Final[Hour of Day], 60 - MINUTE ( Final[start] ), IF ( HOUR ( Final[end] ) = Final[Hour of Day], MINUTE ( Final[end] ), 60 ) ) )
Regards,
Cherie
Hi
I just found out, that whith that formula I have problem, when dowtime if from 23PM to 1AM. It will just filter such lines out. As I'm new in DAX, I don't know ho to build condition to make it working.
Mathematicaly I will do it like that and add it to original formula:
IF DAY(start)<>DAY(end) THEN HOUR(start)<=(hour of day) AND HOUR(end)>=(hour of day)
But in DAX syntax I don't know. So even small hint with syntax can help me.
Many thanks
Michal
I would suggest you create a new thread on forum so that more community members can see it and provide advice. Please remember to post dummy data and desired result.
Regards,
Cherie
Thank you, it helps a lot. Just can you please help me how I should create IF condition, when I want to have it more precise?
Because I have time for example start 11:50:28 - end 11:53:10. Now I have result Duration=3. And if I want it like Duration=2.7 can you help me how to build it?
Many thanks
Michal
Because I need it more precise, I change it a little to calculate seconds. It may help to some one. But still it's based on @v-cherch-msft idea and I really thank you for this big help.
duration in hour = IF(HOUR(Final[start])=HOUR( Final[end]);
(MINUTE( Final[end]-Final[start]))*60+SECOND(Final[end]-Final[start]);
IF(HOUR(Final[start] ) = Final[Hour of Day];
3600-((MINUTE(Final[start])*60)+SECOND(Final[start]));
IF(HOUR(Final[end] ) = Final[Hour of Day];
(MINUTE(Final[end])*60)+SECOND(Final[end]);3600)))
Covering 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 |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |