cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 12 Hour Service Hours Spanning to Next day DAX

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:0007 Mar 18:00 - 06:0008 Mar 06:00 - 18:0008 Mar 18:00 - 06:0009 Mar 06:00 - 18:0009 Mar 18:00 - 06:00

1 ACCEPTED SOLUTION
Frequent Visitor

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 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``````

2 REPLIES 2
Frequent Visitor

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 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``````

Community Support

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))
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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.