Filter day based on 6am to 6am hrs instead of 0 to 24hrs

[TejasShah]

Hello all, so I have these shop floor data where in we have data taken for every hour of the day for each individual meter. The shop works in 3 shifts, from 6am to 2pm - S1, 2pm to 10pm - S2 and 10pm to 6am - S3.

For now I have all my visuals based on timestamps(bins) which I have created as hourly basis. But I want the day to begin with day1 6:00 instead of 00:00 and end with day2 05:59 instead of day1 23:59.

Find below my data table,

MeterId	Meter_Name	Values	timestamp(bins)
1	Meter1	12.13	22-02-2024 21:00
2	Meter2	1.81	22-02-2024 21:00
3	Meter3	960	22-02-2024 21:00
4	Meter4	52.59	22-02-2024 21:00
5	Meter5	1.24	22-02-2024 21:00
6	Meter6	23	22-02-2024 21:00
7	Meter7	10.09	22-02-2024 21:00
8	Meter8	8.38	22-02-2024 21:00
9	Meter9	0.81	22-02-2024 21:00
10	Meter10	4.56	22-02-2024 21:00
1	Meter1	0.81	22-02-2024 22:00
2	Meter2	1.08	22-02-2024 22:00
3	Meter3	0.22	22-02-2024 22:00
4	Meter4	56.5	22-02-2024 22:00
5	Meter5	14.66	22-02-2024 22:00
6	Meter6	7.83	22-02-2024 22:00
7	Meter7	6.82	22-02-2024 22:00
8	Meter8	290	22-02-2024 22:00
9	Meter9	2464	22-02-2024 22:00
10	Meter10	0.48	22-02-2024 22:00
1	Meter1	2	22-02-2024 23:00
2	Meter2	13.66	22-02-2024 23:00
3	Meter3	4224	22-02-2024 23:00
4	Meter4	568	22-02-2024 23:00
5	Meter5	13	22-02-2024 23:00
6	Meter6	184.38	22-02-2024 23:00
7	Meter7	22.25	22-02-2024 23:00
8	Meter8	2624	22-02-2024 23:00
9	Meter9	27136	22-02-2024 23:00
10	Meter10	2.75	22-02-2024 23:00
1	Meter1	0	23-02-2024 00:00
2	Meter2	4.38	23-02-2024 00:00
3	Meter3	1.68	23-02-2024 00:00
4	Meter4	6.1	23-02-2024 00:00
5	Meter5	0	23-02-2024 00:00
6	Meter6	0	23-02-2024 00:00
7	Meter7	29.75	23-02-2024 00:00
8	Meter8	0.36	23-02-2024 00:00
9	Meter9	0.24	23-02-2024 00:00
10	Meter10	228	23-02-2024 00:00
1	Meter1	59.05	23-02-2024 01:00
2	Meter2	1120	23-02-2024 01:00
3	Meter3	1.94	23-02-2024 01:00
4	Meter4	7.25	23-02-2024 01:00
5	Meter5	2.48	23-02-2024 01:00
6	Meter6	299	23-02-2024 01:00
7	Meter7	720	23-02-2024 01:00
8	Meter8	0.2	23-02-2024 01:00
9	Meter9	1.2	23-02-2024 01:00
10	Meter10	1184	23-02-2024 01:00
1	Meter1	0	23-02-2024 02:00
2	Meter2	0.35	23-02-2024 02:00
3	Meter3	0.41	23-02-2024 02:00
4	Meter4	1.47	23-02-2024 02:00
5	Meter5	0	23-02-2024 02:00
6	Meter6	1216	23-02-2024 02:00
7	Meter7	2304	23-02-2024 02:00
8	Meter8	150.25	23-02-2024 02:00
9	Meter9	12160	23-02-2024 02:00
10	Meter10	49.28	23-02-2024 02:00
1	Meter1	3.95	23-02-2024 03:00
2	Meter2	37.25	23-02-2024 03:00
3	Meter3	1104	23-02-2024 03:00
4	Meter4	2688	23-02-2024 03:00
5	Meter5	28.25	23-02-2024 03:00
6	Meter6	2.58	23-02-2024 03:00
7	Meter7	0	23-02-2024 03:00
8	Meter8	12544	23-02-2024 03:00
9	Meter9	0	23-02-2024 03:00
10	Meter10	0.31	23-02-2024 03:00
1	Meter1	2.82	23-02-2024 04:00
2	Meter2	0	23-02-2024 04:00
3	Meter3	2.92	23-02-2024 04:00
4	Meter4	12.81	23-02-2024 04:00
5	Meter5	16.5	23-02-2024 04:00
6	Meter6	0.21	23-02-2024 04:00
7	Meter7	21.31	23-02-2024 04:00
8	Meter8	2.12	23-02-2024 04:00
9	Meter9	7.72	23-02-2024 04:00
10	Meter10	7.56	23-02-2024 04:00
1	Meter1	3.02	23-02-2024 05:00
2	Meter2	4.76	23-02-2024 05:00
3	Meter3	0.65	23-02-2024 05:00
4	Meter4	6.98	23-02-2024 05:00
5	Meter5	0	23-02-2024 05:00
6	Meter6	0	23-02-2024 05:00
7	Meter7	0.13	23-02-2024 05:00
8	Meter8	0.25	23-02-2024 05:00
9	Meter9	1.09	23-02-2024 05:00
10	Meter10	38.74	23-02-2024 05:00
1	Meter1	0	23-02-2024 06:00
2	Meter2	0	23-02-2024 06:00
3	Meter3	0.34	23-02-2024 06:00
4	Meter4	7.06	23-02-2024 06:00
5	Meter5	1.78	23-02-2024 06:00
6	Meter6	1664	23-02-2024 06:00
7	Meter7	0.12	23-02-2024 06:00
8	Meter8	0	23-02-2024 06:00
9	Meter9	17024	23-02-2024 06:00
10	Meter10	1104	23-02-2024 06:00
1	Meter1	5.6	23-02-2024 07:00
2	Meter2	5.84	23-02-2024 07:00
3	Meter3	1136	23-02-2024 07:00
4	Meter4	22.19	23-02-2024 07:00
5	Meter5	6400	23-02-2024 07:00
6	Meter6	0.66	23-02-2024 07:00
7	Meter7	1312	23-02-2024 07:00
8	Meter8	0.15	23-02-2024 07:00
9	Meter9	1.79	23-02-2024 07:00
10	Meter10	14.56	23-02-2024 07:00
1	Meter1	8.44	23-02-2024 08:00
2	Meter2	34.34	23-02-2024 08:00
3	Meter3	1.74	23-02-2024 08:00
4	Meter4	0.04	23-02-2024 08:00
5	Meter5	0.2	23-02-2024 08:00
6	Meter6	0.69	23-02-2024 08:00
7	Meter7	0.27	23-02-2024 08:00
8	Meter8	0.24	23-02-2024 08:00
9	Meter9	5888	23-02-2024 08:00
10	Meter10	23.63	23-02-2024 08:00
1	Meter1	29.5	23-02-2024 09:00
2	Meter2	1.72	23-02-2024 09:00
3	Meter3	12.81	23-02-2024 09:00
4	Meter4	0.71	23-02-2024 09:00
5	Meter5	0	23-02-2024 09:00
6	Meter6	1.7	23-02-2024 09:00
7	Meter7	0	23-02-2024 09:00
8	Meter8	3008	23-02-2024 09:00
9	Meter9	0.6	23-02-2024 09:00
10	Meter10	80	23-02-2024 09:00
1	Meter1	1.06	23-02-2024 10:00
2	Meter2	0	23-02-2024 10:00
3	Meter3	1.61	23-02-2024 10:00
4	Meter4	1.94	23-02-2024 10:00
5	Meter5	0	23-02-2024 10:00
6	Meter6	0.27	23-02-2024 10:00
7	Meter7	0	23-02-2024 10:00
8	Meter8	6.46	23-02-2024 10:00
9	Meter9	22.38	23-02-2024 10:00
10	Meter10	8.32	23-02-2024 10:00
1	Meter1	16.28	23-02-2024 11:00
2	Meter2	89	23-02-2024 11:00
3	Meter3	55.25	23-02-2024 11:00
4	Meter4	6	23-02-2024 11:00
5	Meter5	47	23-02-2024 11:00
6	Meter6	0	23-02-2024 11:00
7	Meter7	0	23-02-2024 11:00
8	Meter8	0	23-02-2024 11:00
9	Meter9	340	23-02-2024 11:00
10	Meter10	6656	23-02-2024 11:00
1	Meter1	5.38	23-02-2024 12:00
2	Meter2	12.06	23-02-2024 12:00
3	Meter3	0	23-02-2024 12:00
4	Meter4	0.08	23-02-2024 12:00
5	Meter5	1.07	23-02-2024 12:00
6	Meter6	0	23-02-2024 12:00
7	Meter7	3104	23-02-2024 12:00
8	Meter8	0.21	23-02-2024 12:00
9	Meter9	1248	23-02-2024 12:00
10	Meter10	5.5	23-02-2024 12:00
1	Meter1	88	23-02-2024 13:00
2	Meter2	0	23-02-2024 13:00
3	Meter3	1.82	23-02-2024 13:00
4	Meter4	1.17	23-02-2024 13:00
5	Meter5	9.11	23-02-2024 13:00
6	Meter6	21.38	23-02-2024 13:00
7	Meter7	146.75	23-02-2024 13:00
8	Meter8	0	23-02-2024 13:00
9	Meter9	0.19	23-02-2024 13:00
10	Meter10	97	23-02-2024 13:00
1	Meter1	13.59	23-02-2024 14:00
2	Meter2	51.34	23-02-2024 14:00
3	Meter3	0	23-02-2024 14:00
4	Meter4	0.09	23-02-2024 14:00
5	Meter5	880	23-02-2024 14:00
6	Meter6	22784	23-02-2024 14:00
7	Meter7	7.05	23-02-2024 14:00
8	Meter8	1.9	23-02-2024 14:00
9	Meter9	21.03	23-02-2024 14:00
10	Meter10	0.13	23-02-2024 14:00
1	Meter1	1344	23-02-2024 15:00
2	Meter2	0	23-02-2024 15:00
3	Meter3	1216	23-02-2024 15:00
4	Meter4	10.79	23-02-2024 15:00
5	Meter5	24.19	23-02-2024 15:00
6	Meter6	0	23-02-2024 15:00
7	Meter7	0.26	23-02-2024 15:00
8	Meter8	4.44	23-02-2024 15:00
9	Meter9	69.88	23-02-2024 15:00
10	Meter10	1.93	23-02-2024 15:00
1	Meter1	0.49	23-02-2024 16:00
2	Meter2	3.25	23-02-2024 16:00
3	Meter3	6.1	23-02-2024 16:00
4	Meter4	15.38	23-02-2024 16:00
5	Meter5	0	23-02-2024 16:00
6	Meter6	12.13	23-02-2024 16:00
7	Meter7	17.25	23-02-2024 16:00
8	Meter8	1.86	23-02-2024 16:00
9	Meter9	75.31	23-02-2024 16:00
10	Meter10	4.35	23-02-2024 16:00

For my visuals:

I have this daily sum as 0th to 23rd hrs of day1, which I need it to be from 6th hr of day1 to 6th hr of day2.

My date filters as:

And my Pie also shows Meterwise sum but as per universal hours from Meter1 to Meter10:

Request your support for the same,

1. for converting a day into 6 to 6 day

2. dividing that 6 to 6 day into S1, S2, S3 shifts

[TejasShah]

Hello @Anonymous ,

I have tried this below flag measure, but it only solves half of the purpose.

6To6Flag =

VAR vStartDate =

    CALCULATE ( MIN ( Meter_Consumption_Table[timestamp (bins)] ), ALLSELECTED( ( Meter_Consumption_Table[timestamp (bins)] ) ))

VAR vEndDate =

    CALCULATE ( MAX ( Meter_Consumption_Table[timestamp (bins)] ), ALLSELECTED ( Meter_Consumption_Table[timestamp (bins)] ) )

VAR vCurrentLogDatetime =

    MAX ( Meter_Consumption_Table[timestamp (bins)] )

VAR vCurrentLogDate =

    INT ( vCurrentLogDatetime )

VAR vCurrentLogTime =

    TIME ( HOUR ( vCurrentLogDatetime ), MINUTE ( vCurrentLogDatetime ), SECOND ( vCurrentLogDatetime ) )

VAR vResult =

    SWITCH (

        TRUE,

        vCurrentLogDate = vStartDate, IF ( vCurrentLogTime > TIME ( 6, 0, 0 ), 1, 0 ),

        vCurrentLogDate = vEndDate, IF ( vCurrentLogTime < TIME ( 6, 0, 0 ), 1, 0 ),

        vCurrentLogDate > vStartDate && vCurrentLogDate < vEndDate, 1,

        0

    )

RETURN

    vResult

But I can only get 0 for From timestamp(bins) till 6am, but I a not getting 0 after 6am of the To timestamp(bins)

      

Can you please help me out if possible?

Also, @Anonymous I tried your solution but couldn't get what I need. Can you please suggest any alternate way out if possible

[Anonymous]

Hi @TejasShah ,
May I ask if your problem has been solved. If the above reply was helpful, you may consider marking it as solution. If the problem is not yet solved, please feel free to ask us a question.

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

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

[TejasShah]

Hi @Anonymous my problem has not yet been solved, I am still trying out other ways. Will surely reply here with a solution if I get or any questions if I am unable to get my desired results.

Thanks.

[Anonymous]

Create a Time dimension table based on Hours or Hours plus Minutes or Mours plus Minute plus Seconds.
Relate the table to the time at whatever grain you need

Have a column for Sort order like hours + 1 (06-01, 07-02, 08-03, etc.) and make the hour column be sorted by this Sort Order Column.