Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Group datetime data by date and hour for comparison line graph

Hi All,

 

Appreciate if you can help me with this report.

 

I have a raw data as below table. Attached in excel. I would like to create a table and a line graph to show the comparison of difference between actual and incremental count on selected date grouping by hour.

 

jenas6423_0-1600653716227.png

 

Table:

jenas6423_1-1600653808383.png

 

Line Graph:

 

jenas6423_2-1600653825110.png

 

Currently, the data processing is done in SQL, I have created a view for it, which resulted in folllwing table.

 

jenas6423_3-1600653905541.png

 

However, by using this view, I couldn't include slicers for my date selection, as it will sum up the hour by the range of dates that I have selected.. Can anyone advise how to do it? Or how to combine date and hour for line graph?

 

Please advise. Appreciate if anyone can help.

 

The Raw data can be downloaded here:

 

https://drive.google.com/file/d/10ZVdWG5MDYNRnqN22getpe7UNYiZJf_X/view?usp=sharing

 

SQL View Query:

 

SELECT Prodline, IIF(CONVERT(time, update_time, 101) >= '00:00:00' AND CONVERT(time, update_time, 101) < '08:03:00' , CONVERT(DATE, update_time - 1, 101) , CONVERT(DATE, update_time, 101)) As Date ,
IIF(CONVERT(time, update_time, 101) >= '08:03:00' AND CONVERT(time, update_time, 101) <= '20:03:00' , '1st' , '2nd') AS Shift,
DATEPART(hour,update_time) AS Hour,
MAX([incremental_val]) - MIN([incremental_val]) + 1 AS Inc,
MAX([actual_val]) - MIN([actual_val]) + 1 AS Actual,
FORMAT((MAX([actual_val]) - MIN([actual_val]) + 1) - (MAX([incremental_val]) - MIN([incremental_val]) + 1),'+0;-0') As Diff
FROM [MES].[dbo].[PlanDataRecord]
GROUP by IIF(CONVERT(time, update_time, 101) >= '00:00:00' AND CONVERT(time, update_time, 101) < '08:03:00' , CONVERT(DATE, update_time - 1, 101) , CONVERT(DATE, update_time, 101)),
DATEPART(hour,update_time), Prodline, IIF(CONVERT(time, update_time, 101) >= '08:03:00' AND CONVERT(time, update_time, 101) <= '20:03:00' , '1st' , '2nd')

 

date column is modified according to shift time range.

 

I have accumulated the actual and incremental count for line graph:

Acc_Actual =
CALCULATE (
SUM ( 'Progress_Status'[Actual] ),
FILTER (
ALLSELECTED(Progress_Status) ,
'Progress_Status'[Hour] <= MAX ( 'Progress_Status'[Hour])
)
)
 
Acc_Incremental =
CALCULATE (
SUM ( 'Progress_Status'[Inc] ),
FILTER (
ALLSELECTED(Progress_Status) ,
'Progress_Status'[Hour] <= MAX ( 'Progress_Status'[Hour])
)
)
 
My Slicers:
 

Capture.JPG

 

 

 

 

 

 

 

 

 

 

2 REPLIES 2
amitchandak
Super User
Super User

@Anonymous . Not very clear.

Separate the time and then Two option I can think of

Time  = [Update Datetime].time

1. Use the time on-axis and slicer

2. Create a time table and have an hour there. and use that for analysis :https://kohera.be/blog/power-bi/how-to-create-a-time-table-in-power-bi-in-a-few-simple-steps/

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
Anonymous
Not applicable

Hi

 

 

 

Do you know how to group by Production line, hourly with the max(actual_val) - min(actual_val) new actual column ?

 

Please advise. Thank you.

 

Regards.

 

Jenas

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.