How to get customized Bar graph with 6am to 6am filter

[TejasShah]

After going through a lot of posts, I was unable to find any way out for my issue.

I have a table with values and timestamp of multiple rows for single hour:

Id	Value	timestamp
3	10.88	10-04-2024 15:00:59
4	1.72	10-04-2024 15:00:42
5	22.57	10-04-2024 15:00:29
4	0	10-04-2024 15:00:03
3	0.04	10-04-2024 14:00:45
5	10.91	10-04-2024 14:00:19
3	13.68	10-04-2024 13:00:50
4	5.66	10-04-2024 13:00:28
5	3.63	10-04-2024 13:00:11
4	2.52	10-04-2024 12:00:40
5	1.14	10-04-2024 12:00:07
3	47.1	10-04-2024 11:00:56
4	0.12	10-04-2024 11:00:39
3	1.59	10-04-2024 11:00:20
5	0	10-04-2024 11:00:07
4	0.92	10-04-2024 10:00:55
5	142.94	10-04-2024 10:00:17
4	6.16	10-04-2024 09:00:12
5	2.31	10-04-2024 08:00:36
3	0.83	10-04-2024 08:00:13
5	0	10-04-2024 07:00:54
3	6.58	10-04-2024 06:00:25
4	1.39	10-04-2024 05:00:54
3	0.35	10-04-2024 05:00:43
3	0.94	10-04-2024 04:00:29
5	63.5	10-04-2024 04:00:10
5	0	10-04-2024 03:00:52
3	52.13	10-04-2024 02:00:49
4	8.34	10-04-2024 01:00:57
4	9.13	10-04-2024 01:00:46
5	0	10-04-2024 00:00:51
3	2.61	09-04-2024 23:00:23
5	4.09	09-04-2024 22:00:51
5	7.15	09-04-2024 22:00:32
4	0	09-04-2024 22:00:14
3	1.98	09-04-2024 20:00:55
4	30.28	09-04-2024 20:00:42
4	0.16	09-04-2024 20:00:20
5	37.84	09-04-2024 20:00:18
4	3.75	09-04-2024 20:00:05

I have converted these timestamp data to timestamp (bins) where I have kept 1 hour data as 1 bin.

timestamp (bins)

10-04-2024 15:00

10-04-2024 15:00

10-04-2024 15:00

10-04-2024 15:00

10-04-2024 14:00

10-04-2024 14:00

10-04-2024 13:00

10-04-2024 13:00

10-04-2024 13:00

10-04-2024 12:00

10-04-2024 12:00

10-04-2024 11:00

10-04-2024 11:00

10-04-2024 11:00

10-04-2024 11:00

10-04-2024 10:00

10-04-2024 10:00

10-04-2024 09:00

10-04-2024 08:00

10-04-2024 08:00

10-04-2024 07:00

10-04-2024 06:00

10-04-2024 05:00

10-04-2024 05:00

10-04-2024 04:00

10-04-2024 04:00

10-04-2024 03:00

10-04-2024 02:00

10-04-2024 01:00

10-04-2024 01:00

10-04-2024 00:00

09-04-2024 23:00

09-04-2024 22:00

09-04-2024 22:00

09-04-2024 22:00

09-04-2024 20:00

09-04-2024 20:00

09-04-2024 20:00

09-04-2024 20:00

09-04-2024 20:00

I also have the Flag6to6 where in I get data only from 6am current day to 6am of next day. Using timestamp (bins) and these Flag6to6 measure, I am able to get a bar graph where I have daily data plot as per timestamp (bins) from 6am of 9th to 5am of 10th.

What I need is:

1. How to club these bars with date heirarchy. I can get month/day heirarchy in timestamp but not in timestamp (bins)

2. Is it possible to get one single bar with sum of values from 6am 9th to 5am of 10th ?

Please support, thank you.

[v-zhouwen-msft · Accepted solution]

Hi @TejasShah ,

Regarding your question, here is my understanding.

1.How to club these bars with date heirarchy. 

After my attempts, it's true that you can't view the date hierarchy after grouping, you might want to try another method.

Create a date calculation column using the following dax expression.

Column = DATE(YEAR([timestamp]),MONTH([timestamp]),DAY([timestamp])) + TIME(HOUR([timestamp]),MINUTE([timestamp]),00)

2.Is it possible to get one single bar with sum of values from 6am 9th to 5am of 10th ?

Create a date calculation column using the following dax expression.

Column 2 = SWITCH(TRUE(),
[Column]  >= DATE(2024,4,9) + TIME(6,00,00) && [Column] <=DATE(2024,4,10) + TIME(5,00,00)," 6am 9th to 5am of 10th",
"Other")

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

[TejasShah]

@v-zhouwen-msft thank you so much for your support. The 1st part of the topic is prefectly solved as per your inputs and suggestion of creating an additional timestamp column using Dax.

But the only issue with the 2nd part, where-in you have used calculated Dax column for showcasing single bar graph for the single day as per my requirement. I am sorry since I didn't mention the wider scope.

The calculated Dax column could give me bar graph from 6am of 9th to 5am of 10th, but similarly I wanted to get it for all the days selected in the slicer.

For example, if I select 7th to 11th date in the slicer, can I get individual bars in the same graph with one bar for 7th to 8th (6am to 5am sum), second bar with 8th to 9th (6am to 5am sum) and so on.

Thank you once again for your support and help.

[v-zhouwen-msft · Accepted solution]

Hi @TejasShah ,

Regarding your question, the data are as follows.

Use the following DAX expression to create a column

Range = IF(HOUR([Date]) >=6,DATE(YEAR([Date]),MONTH([Date]),DAY([Date])),DATE(YEAR([Date]),MONTH([Date]),DAY([Date])) - 1 )

Final output

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

[TejasShah]

@v-zhouwen-msft thank you very much, I was able to get what exactly I needed, and I also learned a lot about how can we play around with Timestamp columns as well, as per our requirements.

Thank you once again.

[v-zhouwen-msft · Accepted solution]

Hi @TejasShah ,

Regarding your question, here is my understanding.

1.How to club these bars with date heirarchy. 

After my attempts, it's true that you can't view the date hierarchy after grouping, you might want to try another method.

Create a date calculation column using the following dax expression.

Column = DATE(YEAR([timestamp]),MONTH([timestamp]),DAY([timestamp])) + TIME(HOUR([timestamp]),MINUTE([timestamp]),00)

2.Is it possible to get one single bar with sum of values from 6am 9th to 5am of 10th ?

Create a date calculation column using the following dax expression.

Column 2 = SWITCH(TRUE(),
[Column]  >= DATE(2024,4,9) + TIME(6,00,00) && [Column] <=DATE(2024,4,10) + TIME(5,00,00)," 6am 9th to 5am of 10th",
"Other")

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