Forum Discussion
Core and non core hrs calculated column
Hi,
Can someone help me with a calulated column dax where I can have event's core hrs and how many non core hrs we had extra.
Hi Anonymous ,
Here are the steps you can follow：
1. In Power Query - Select [ReserverdStartDT] and [ReserverdEndDT] respectively - Time - Time only..
2. Create calculated table.
Time = VAR __StartTime= TIME(0,0,0) var __EndTime= TIME(23,0,0) var __Duration= TIME(1,0,0) return GENERATESERIES(__StartTime,__EndTime,__Duration)Create calculated column.
Group = IF( [Value]>= TIME(8,0,0) && [Value] <= TIME(18,0,0),"core","non-core")3. Create calculated column.
core_flag = COUNTX( FILTER(ALL('Time'), 'Time'[Value]>=[Time1]&&'Time'[Value]<=[Time2]&&'Time'[Group]="core"),[Group])non-core_flag = COUNTX( FILTER(ALL('Time'), 'Time'[Value]>=[Time1]&&'Time'[Value]<=[Time2]&&'Time'[Group]="non-core"),[Group])4. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
3 Replies
- amitchandak
Super User
Anonymous , new columns
Core = datediff([reservationStartDT], [ReservationEndDT], minute)/60
non Core = datediff([CoreHrsStartDT], [CoreHrsEndDT], minute)/60
change column names as per need
Thank you amitchandak but it's not what I need. Probably you need more info.
Core hrs for events are 8 am to 6 pm and non-core hrs are fro 6pm to 8am.
What I need to find out is from reserved dates how many hrs the reservations were in the core hrs and how many in non core hrs (2 separate columns)
This is what I'm thinking but I can`t put it together.
If [reservationStartDT]>=[CoreHrsStartDT] and if [ReservationEndDT]<= [CoreHrsEndDT] then datediff([reservationStartDT], [ReservationEndDT], minute)/60
If [reservationStartDT]<=[CoreHrsStartDT] and if [ReservationEndDT]>= [CoreHrsEndDT] then datediff([reservationStartDT], [ReservationEndDT], minute)/60
But also I have reservations which start on core hrs and finish non-core hrs. Ex. 4 pm to 8 pm. this should be 2 core hrs and 2 non core hrs. Or 6 am to 9 am (1 core hrs and 2 non core hrs)
Hi Anonymous ,
Here are the steps you can follow：
1. In Power Query - Select [ReserverdStartDT] and [ReserverdEndDT] respectively - Time - Time only..
2. Create calculated table.
Time = VAR __StartTime= TIME(0,0,0) var __EndTime= TIME(23,0,0) var __Duration= TIME(1,0,0) return GENERATESERIES(__StartTime,__EndTime,__Duration)Create calculated column.
Group = IF( [Value]>= TIME(8,0,0) && [Value] <= TIME(18,0,0),"core","non-core")3. Create calculated column.
core_flag = COUNTX( FILTER(ALL('Time'), 'Time'[Value]>=[Time1]&&'Time'[Value]<=[Time2]&&'Time'[Group]="core"),[Group])non-core_flag = COUNTX( FILTER(ALL('Time'), 'Time'[Value]>=[Time1]&&'Time'[Value]<=[Time2]&&'Time'[Group]="non-core"),[Group])4. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
