Group datetime data by date and hour for comparison line graph

[Anonymous]

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.

Table:

Line Graph:

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

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:

[amitchandak]

@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/

[Anonymous]

Hi amitchandak,

Thank you for your reply. Im sorry if Im not being clear.

I have created a new column by date & hours from the initial update_time column as below:

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