Shifted Calendar Date and Time

[Mramono]

I have normal calendar table and Time table, although my Fact Table has date and time in different columns, my day starts at 6am and ends at 6am the following day. The first production hour of a day is from 6am to 7am, so 07:00 shows production from 6am to 7am

I need a calendar table and time table that can be able to plot my production in terms of production date when i choose and calendar date when i choose that option as well. Knowing production date start from 6am to 6am the next day and calendar date starts from 12am to 23pm. Also tracking Shifts (Morningshift 06:00 to 14:00, Afternoonshift 14:00 to 22:00, Nightshift 22:00 to 06:00). The aim is to be able to slice production in terms of date and time (hours) 

Calendar Date	Production Date	Time	Shift	Tons	%Rec
28-Jul-25	28-Jul-25	07:00	NS	905	61.25
28-Jul-25	28-Jul-25	08:00	NS	1123	61.25
28-Jul-25	28-Jul-25	09:00	NS	1198	64.04
28-Jul-25	28-Jul-25	10:00	NS	1117	64.04
28-Jul-25	28-Jul-25	11:00	NS	1137	64.36
28-Jul-25	28-Jul-25	12:00	NS	1152	64.36
28-Jul-25	28-Jul-25	13:00	NS	1110	63.92
28-Jul-25	28-Jul-25	14:00	NS	1128	63.92
28-Jul-25	28-Jul-25	15:00	NS	1140	61
28-Jul-25	28-Jul-25	16:00	NS	1094	63.5
28-Jul-25	28-Jul-25	17:00	NS	1123	64.85
28-Jul-25	28-Jul-25	18:00	NS	1135	64.85
28-Jul-25	28-Jul-25	19:00	NS	1120	64.35
28-Jul-25	28-Jul-25	20:00	NS	1096	64.35
28-Jul-25	28-Jul-25	21:00	NS	1042	64.58
28-Jul-25	28-Jul-25	22:00	NS	1008	64.58
28-Jul-25	28-Jul-25	23:00	NS	1045	64.04
29-Jul-25	28-Jul-25	00:00	NS	1015	64.04
29-Jul-25	28-Jul-25	01:00	NS	504	63.83
29-Jul-25	28-Jul-25	02:00	NS	1089	63.83
29-Jul-25	28-Jul-25	03:00	NS	1137	64.06
29-Jul-25	28-Jul-25	04:00	NS	1226	64.06
29-Jul-25	28-Jul-25	05:00	NS	1250	64.07
29-Jul-25	28-Jul-25	06:00	NS	1249	64.07
29-Jul-25	29-Jul-25	07:00	NS	1114	64.2
29-Jul-25	29-Jul-25	08:00	NS	817	64.2
29-Jul-25	29-Jul-25	09:00	NS	1072	63.53
29-Jul-25	29-Jul-25	10:00	NS	1100	63.53
29-Jul-25	29-Jul-25	11:00	NS	1115	64.22
29-Jul-25	29-Jul-25	12:00	NS	1200	64.22
29-Jul-25	29-Jul-25	13:00	NS	1153	63.79
29-Jul-25	29-Jul-25	14:00	NS	1151	63.79
29-Jul-25	29-Jul-25	15:00	NS	1134	63.41
29-Jul-25	29-Jul-25	16:00	NS	1107	63.41
29-Jul-25	29-Jul-25	17:00	NS	1158	63.78
29-Jul-25	29-Jul-25	18:00	NS	1187	63.78
29-Jul-25	29-Jul-25	19:00	NS	616	63.41
29-Jul-25	29-Jul-25	20:00	NS	0	63.41
29-Jul-25	29-Jul-25	21:00	NS	0	63.78
29-Jul-25	29-Jul-25	22:00	NS	934	63.78
29-Jul-25	29-Jul-25	23:00	NS	1104	63.43
30-Jul-25	29-Jul-25	00:00	NS	130	63.43
30-Jul-25	29-Jul-25	01:00	NS	1099	63.79
30-Jul-25	29-Jul-25	02:00	NS	1235	63.79
30-Jul-25	29-Jul-25	03:00	NS	1140	63.92
30-Jul-25	29-Jul-25	04:00	NS	1047	63.92
30-Jul-25	29-Jul-25	05:00	NS	1243	63.14
30-Jul-25	29-Jul-25	06:00	NS	1250	63.14
30-Jul-25	30-Jul-25	07:00	NS	737	63.32
30-Jul-25	30-Jul-25	08:00	NS	1366	63.32
30-Jul-25	30-Jul-25	09:00	NS	1392	63.34
30-Jul-25	30-Jul-25	10:00	NS	1216	63.34
30-Jul-25	30-Jul-25	11:00	NS	952	64.92
30-Jul-25	30-Jul-25	12:00	NS	858	64.92
30-Jul-25	30-Jul-25	13:00	NS	1387	64.46
30-Jul-25	30-Jul-25	14:00	NS	1473	64.46
30-Jul-25	30-Jul-25	15:00	NS	439	63.94
30-Jul-25	30-Jul-25	16:00	NS	244	63.94
30-Jul-25	30-Jul-25	17:00	NS	571	64.63
30-Jul-25	30-Jul-25	18:00	NS	864	64.63
30-Jul-25	30-Jul-25	19:00	NS	436	65.24
30-Jul-25	30-Jul-25	20:00	NS	996	65.24
30-Jul-25	30-Jul-25	21:00	NS	1052	64.28
30-Jul-25	30-Jul-25	22:00	NS	796	64.28
30-Jul-25	30-Jul-25	23:00	NS	1210	65.09
30-Jul-25	30-Jul-25	00:00	NS	1448	65.09
31-Jul-25	30-Jul-25	01:00	NS	1452	63.98
31-Jul-25	30-Jul-25	02:00	NS	253	63.98
31-Jul-25	30-Jul-25	03:00	NS	1053	64.92
31-Jul-25	30-Jul-25	04:00	NS	1099	64.92
31-Jul-25	30-Jul-25	05:00	NS	790	64.7
31-Jul-25	30-Jul-25	06:00	NS	885	64.7

[v-pnaroju-msft]

Hi Mramono,

Thank you for your followup.

The difference in sorting arises from how the HourLabel field is handled. In my sample file, the HourLabel column is sorted by a numeric ProdHourSort column (0–23). In your file, HourLabel is a text field only, so Power BI sorts it alphabetically, which explains the apparently random order.

Please follow the steps below to resolve the issue:

1. Select the HourLabel column in the Fields pane.
2. On the ribbon, go to Column tools, select Sort by Column, and choose ProdHourSort.
3. Use HourLabel on the X-axis of the chart.

Please find attached a screenshot to assist you in resolving the issue:

We hope  that the information provided will help to resolve the problem. Should you have any further queries, please feel free to contact the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft · Accepted solution]

Thankyou, @Irwan, for your response.

Hi Mramono,

We appreciate your inquiry to the Microsoft Fabric Community Forum.

Based on my understanding of the issue, please find attached a screenshot and a sample PBIX file that may help resolve the problem:

We hope the information provided is helpful. If you have any further queries, please feel free to contact the Microsoft Fabric Community.

Thank you.

[Mramono]

Hi @v-pnaroju-msft 

I have tried to replecate the file you send me. but for some reason i am not getting the same sorting of hours like the one you send me. I'm not sure if there is a particular sorting i am missing. my HourLabel on the x-axis seem to be random. How can i resolvel this. The link below is my Powerbi File

https://drive.google.com/file/d/10spLazfjkhPhd-TpZ5wyDQV8Rp8-grxg/view?usp=sharing 

Much appreciated!

Mramono

[Syndicate_Admin]

Hi, thank you so much for the help. I hope to have a time series display and use both the production date and the shift period as the cutter as you have shown me. The idea is to have the hourly view of the data for a particular production date. The production day is not the same as the calendar day. I'm not sure if the graph below can help clarify it.

[Mramono]

Thanks again, just to clarify some more

Production Date is not the same as Calendar date. There is an overlap between the two. On the Production date the date changes at 6am (06:00) as opposed to Calendar date where the date changes at 12am (00:00)

Can we modify the calendar table to reflect PRODUCTION DATE not CALENDAR DATE.

Production date overlaps with Calender date (by shifting calendar date by 6hrs)

[Irwan]

hello @Mramono 

i might be misunderstood, but from your description, i think you can use production date as date slicer selection.

as for tracking shift, perhaps you want to create another calculated column to define those shift time.

something like below.

then you can plot into a visual you prefered.

Hope this will help.

Thank you.

[Mramono]

Hi Irwan, thank you very much for the assistance. I am hoping to have a time series visualisation and use both the production date and shift period as the slicer like you have shown me. The idea is to have the hourly view of the data for a particular production date. Production day is not the same as calendar day. Not sure if the graph below can help clarify.