12 Hour Service Hours Spanning to Next day DAX

[vj82reddy]

Thanks in advance,
Here below is one sample of logic expected,

example:

Service Hours 6 am to 18 pm (12Hour) falls into same day bucket no issue to bucket this for same day.

but for 18 pm to 23 is on same day and 0 to 6am of Next day to bucket into current day (12 hour)

can some one suggest me a DAX logic to achieve this logic.

07 Mar 06:00 - 18:00
07 Mar 18:00 - 06:00
08 Mar 06:00 - 18:00
08 Mar 18:00 - 06:00
09 Mar 06:00 - 18:00
09 Mar 18:00 - 06:00

[vj82reddy · Accepted solution]

Thanks alot,

this looks a table valued function which is very expensive, but i did it some how with 2 fields with in the same table.

_ShiftDate = 
VAR Curr18 =IF(MyFactTable[_Hour]IN{18,19,20,21,22,23} ,2,0)
VAR Bucket0_5 =IF(MyFactTable[_Hour]IN{0,1,2,3,4,5} ,1,0)
VAR DateChange = DATEADD(MyFactTable[Datetime].[Date],-1,DAY)
VAR Day_1 = IF(Bucket0_5=1 && MyFactTable[Datetime].[Date]<=MyFactTable[_NxtDay].[Date],DateChange,MyFactTable[Datetime].[Date])
VAR Shift = IF(Bucket0_5=1 && Curr18=2 ,"18-06","6-18")
RETURN  Day_1

_Shift = 
VAR A = IF(MyFactTable[_Hour] IN{0,1,2,3,4,5,18,19,20,21,22,23},"18:00-06:00","06:00-18:00")
RETURN  A

[v-xinruzhu-msft]

Hi @vj82reddy 

You can refer to the following example

You can create a new calculated table

Table 2 = var a=ADDCOLUMNS('Table',"reult",var a=TIMEVALUE(LEFT([Time],5))
var b=TIMEVALUE(RIGHT([Time],5))
return IF(HOUR(b)+12>24,1,0))
var c= ADDCOLUMNS(FILTER(a,[reult]=0),"Times",LEFT([Time],5)&"-"&"23:00")
var d= ADDCOLUMNS(FILTER(a,[reult]=0),"dates",[Date]+1,"Times","00:00"&"-"&RIGHT([Time],5))
return UNION(SUMMARIZE(FILTER(a,[reult]=1),[Date],'Table'[Time]),SELECTCOLUMNS(c,"Dates",[Date],"Time",[Times]),SELECTCOLUMNS(d,"Dates",[dates],"Time",[Times]))

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.