Yield calculation with datetime ranges and slicer

[PaulFICOT]

Hello everyone,

CONTEXT

I am currently trying to calculate the yield of an industrial machine.

The machine append a line into a database everytime it does something (start a production cycle, maintenance cycle, idle, etc.)

The fact table is shaped like this:

MachineID	StateID	StateValue	StateBegin	StateEnd	StateDuration
M-001	1	Production	29/06/2022 10:30:53	01/07/2022 14:33:32	3122
M-001	2	Idle	01/07/2022 14:33:32	01/07/2022 14:45:06	11
M-001	3	Maintenance	01/07/2022 14:45:06	01/07/2022 20:10:03	324
M-001	4	Idle	01/07/2022 20:10:03	04/07/2022 06:03:14	3473

I use a date table and want with a slicer of it retrieve only the values that are in the range.

Here is a little scheme of it and the 4 cases i have identified.

Label

Begin --> StateBegin Column in the table

End --> StateEnd Column in the table

Min --> Slicer's lower boundary

Max --> Slicer's higher boundary

CURRENT ADVANCEMENT

I created 2 measures that returns the min and max of the slicer:

Max= CALCULATE(MAX(Dim_temps[Date]),ALLSELECTED(Dim_temps))

Min= CALCULATE(MIN(Dim_temps[Date]),ALLSELECTED(Dim_temps))

And I started working on 2 measures that will adapt the state's begin and end datetimes depending on slicer's value

The "begin date" kinda works:

NEW BEGIN DATE =

IF(

SELECTEDVALUE ( 'States'[StateBegin] ) < Dim_temps[Min],

Dim_temps[Min],

SELECTEDVALUE ( 'States'[StateBegin] )

)

Unfortunately the "End date" doesnt work with the equivalent formula:

NEW END DATE =

IF(

Dim_temps[Max] < SELECTEDVALUE('States'[StateEnd]),

Dim_temps[Max],

SELECTEDVALUE('States'[StateEnd])

)

And it also does not cover the State 4 case (in photo).

Do you have any idea on how to do this ?

MY GOAL

My ultimate goal would be to recover the time in the slicer's range and exclude the grayed areas in the picture.

Maybe it is possible to do it by splitting every multiple day states in multiple single day line. 

But I have not seen any Power Query, DAX or SQL code allowing you to do this efficiently AND keeping last day's time.

Thank you for your help.

Regards,

Paul

[v-kkf-msft]

Hi @PaulFICOT ,

Please try the custom column in Power Query.

= let BeginDate = Date.From([StateBegin]), EndDate = Date.From([StateEnd])
in List.Dates(BeginDate, Duration.Days(EndDate - BeginDate) + 1,#duration(1, 0, 0, 0))

Then expand the list to new rows.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PaulFICOT]

Hello @v-kkf-msft,

It is almost what I need.

The only issue is that the time information disapear.

I want to find a way to split my multiple day datetime ranges in single day with the date.

I see two ways of doing it :

1. Split and list into two datetime columns
2. Split and list into one date column and add a duration column next to it

Example with the StateID 1 from your PBIX:

Your solution:

Result needed:

SOLUTION1

MachineID	StateID	StateValue	StateBegin	StateEnd	StateDuration	DateList
M-001	1	Production	29/06/2022 10:30:53	01/07/2022 14:33:32	809	29/06/2022
M-001		Production	29/06/2022 10:30:53	01/07/2022 14:33:32	1439	30/06/2022
M-001	1	Production	29/06/2022 10:30:53	01/07/2022 14:33:32	873	01/07/2022

SOLUTION2

MachineID	StateID	StateValue	StateBegin	StateEnd	StateDuration	DateListNegin	DateListEnd
M-001	1	Production	29/06/2022 10:30:53	01/07/2022 14:33:32	3122	29/06/2022 10:33:53	29/06/2022 23:59:59
M-001		Production	29/06/2022 10:30:53	01/07/2022 14:33:32	3122	30/06/2022 00:00:00	30/06/2022 23:59:59
M-001	1	Production	29/06/2022 10:30:53	01/07/2022 14:33:32	3122	01/07/2022 00:00:00	01/07/2022 14:33:32

If you have any other idea 🙂

Regards,

[v-kkf-msft · Accepted solution]

Hi @PaulFICOT ,

Please add these custom columns.

DateListNegin = if DateTime.Date( [StateBegin] ) = [DateList] then [StateBegin] else DateTime.From( [DateList] & #time(0,0,0))

DateListEnd = if DateTime.Date( [StateEnd] ) = [DateList] then [StateEnd] else DateTime.From( [DateList] & #time(23,59,59))

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.