I calculate in hours between two different record dates

[Syndicate_Admin]

Good morning community, I would need the following help.

Currently I have different order numbers and in the factory they would need to know the difference in hours between the end of one order and the beginning of the next. I have created a simplicated table to give you an idea:

The time of the first order is white since we do not have a previous order to be able to do the calculation.

This would be the calculation to make:

Current Order Number D420019A = End Date of Previous Order D433953AC - Current Order Start Date D420019A

Current order number D420019C= End date of previous order D420019A- Start date of current order D420019C

Thank you very much for your help

[Syndicate_Admin]

First of all apologize for not having responded before but last week I was involved and did not have time to try the code you had sent me.

First of all, thank you very much for the help, try the code and perfect.

As for the debug, from what I see here you can't make a console.log and see what the variable is worth at that time or put a breakpoint and ask for the value of a specific variable, right?

Anyway, again thank you very much.

[tamerj1]

Hi Admin @Syndicate_Admin 

thank you for your follow up. 
@RWRW 

You can export you model to excel for furthe debugging. But I guess you should have some kind of power bi product license. 

[Syndicate_Admin]

Hello again

I have a doubt.

After I have finished I have been asked to add a filter to the report in which the user can select a date range.

Assuming there are 3 records like this table that I previously attached to you:

If the user selects from 1/3/2022 to 1/6/2022 he should give me a single record (Last record of the table) and in the empty time calculation since it is the only record returned.

When I make the selection, visually on the screen I only see a record but with the time calculation of 9.14 in the time field since although visually we do not see it the dataset still has loaded all the records. Would there be any way to refresh the dataset when the date selection is made?

I hope I have explained thanks

Best regards

[tamerj1]

@Syndicate_Admin 

@RWRW 

in this case it has to be a measure. A calculated column won't work. I will adjust the code and send it to you

[Syndicate_Admin]

Thank you so much.

I have created measure called InDateRange that returns me 1 in the event that the records I select in the datepicker meet the condition.

InDateRange =

WHERE _rangeStart =

FIRSTDATE ( 'Calendar'[Date] )

WHERE _rangeEnd =

LASTDATE ( 'Calendar'[Date] )

RETURN

IF (

SELECTEDVALUE ( Orders[Start_Date] ) >= _rangeStart

&& SELECTEDVALUE ( Orders[End_date] ) <= _rangeEnd,

1,

0

)

Then I add a filter to the table where I indicate that InDateRange is equal to 1 and return the records that meet the range of selected dates.

Until then, the only thing I would lack and that I do not know how to do is to calculate hours with the current dataset and put it in the Time column.

Thanks again

[Syndicate_Admin]

Good morning

Any suggestions?

Thank you and greetings

[tamerj1]

Hi @Syndicate_Admin @RWRW 

Please try the following

Time Measure = 
VAR SelectedTable = ALLSELECTED ( Orders )
VAR CurrentStart= MAX ( Orders[Start Date] )
VAR PreviousEnd = MAXX ( FILTER ( ALLSELECTED ( Orders ), Orders[Start Date] < CurrentStart ), Orders[End Date] )
VAR TimeDifference = DATEDIFF ( PreviousEnd, CurrentStart, MINUTE )
VAR Result = DIVIDE ( TimeDifference, 60 )
RETURN
    ResulT

[Syndicate_Admin]

Good afternoon

I've been doing a stack of tests by selecting different dates.

The code works but slows down the report a lot, every time you filter and retrieve new records it takes a while to display them on the screen. I don't know what the reason can be.

Any suggestions?

Thanks a lot

[Syndicate_Admin]

In the last test I have done I have filtered for 2 dates in which I only return 4 records, even so it has taken me to load the data, with this means that it is not a problem of how much data is being loaded.

Thank you

[tamerj1]

@Syndicate_Admin  @RWRW 
Please try

Time Measure = 
VAR SelectedTable = ALLSELECTED ( Orders[Start Date], Orders[End Date] )
VAR CurrentStart= MAX ( Orders[Start Date] )
VAR PreviousEnd = MAXX ( FILTER ( SelectedTable, Orders[Start Date] < CurrentStart ), Orders[End Date] )
VAR TimeDifference = DATEDIFF ( PreviousEnd, CurrentStart, MINUTE )
VAR Result = DIVIDE ( TimeDifference, 60 )
RETURN
    ResulT

[Syndicate_Admin]

Hello again

I just tested the code.

I have gone line by line returning the value of each variable with a return, when I have come to return the value of the PreviousEnd variable, it returns all the values of the empty column and it is this variable that slows everything down, it takes about 2 minutes to refresh the report.

Any suggestions? I hope we can find that.

Thank you very much for your help

[tamerj1]

@Syndicate_Admin  @RWRW 
Please try

Time Measure = 
VAR SelectedTable = FILTER ( ALLSELECTED ( Orders[Start Date], Orders[End Date] ), Orders[End Date] <> BLANK ( ) )
VAR CurrentStart= MAX ( Orders[Start Date] )
VAR PreviousEnd = MAXX ( FILTER ( SelectedTable, Orders[Start Date] < CurrentStart ), Orders[End Date] )
VAR TimeDifference = DATEDIFF ( PreviousEnd, CurrentStart, MINUTE )
VAR Result = DIVIDE ( TimeDifference, 60 )
RETURN
    Result

[Syndicate_Admin]

Hello again

Test the code and when I return this line the Time Measure column is displayed like this.

WHERE PreviousEnd = MAXX ( FILTER ( SelectedTable, Pmx[BDL_SIG_TIMESTAMP] < CurrentStart ), Pmx[CPK_SIG_TIMESTAMP] )

Another thing that keeps happening is when I press the Report button that is on the left hand side in Power Bi, a load icon appears at the top left of the visual and it takes about 2 minutes to load into the table.

Thanks a lot

[Syndicate_Admin]

Good morning

I will try to explain myself as best as possible. I've been doing a lot of testing and I've figured out what's going on.

As I told you yesterday, I have a measure called InDateRange that what it does is put a 0 (to those records that are not in the date range) and a 1 (records that are between the selected dates). So far so good.

Then in the visual I annotate it in the filters and I indicate that InDateRange is 1 so that it only shows me those that are in range. I leave you a screenshot of how the table looks and the InDateRange filter.

The columns I have associated with the table are as follows:

and that's how it looks on the screen

My surprise comes now, the measure called InDateRange I do not need to be displayed in the table so I remove the column from the visual and the following happens in the data that is shown.

Now the columns are as follows:

And when you remove the measure in the columns of the table, now the data looks like this

It removes the first row of the table.

As for the code of the calculation of time I am using the last one you passed me and it works correctly.

Time Measure =

// Measure to calculate the time difference between the completion of one batch and the beginning of the next batch

WHERE SelectedTable = FILTER ( ALLSELECTED ( Pmx[Start Date], Pmx[End Date] ), Pmx[End Date] <> BLANK ( ) )

WHERE CurrentStart= MAX(Pmx[Start Date])

WHERE PreviousEnd =MAXX(FILTER (SelectedTable, Pmx[Start Date] < CurrentStart ),Pmx[End Date])

WHERE TimeDifference = DATEDIFF ( PreviousEnd, CurrentStart, MINUTE )

WHERE Result = DIVIDE ( TimeDifference, 60 )

RETURN

Result

I hope you explained me correctly and you can help me solve it.

Thank you very much and I am waiting

[tamerj1]

Hi @Syndicate_Admin @RWRW 
Yes becuase power bi automatically hides the blank measure value. We can use the following simple trick

Time Measure =
// Measure to calculate the time difference between the completion of one batch and the beginning of the next batch
VAR SelectedTable =
    FILTER (
        ALLSELECTED ( Pmx[Start Date], Pmx[End Date] ),
        Pmx[End Date] <> BLANK ()
    )
VAR CurrentStart =
    MAX ( Pmx[Start Date] )
VAR PreviousEnd =
    MAXX ( FILTER ( SelectedTable, Pmx[Start Date] < CurrentStart ), Pmx[End Date] )
VAR TimeDifference =
    DATEDIFF ( PreviousEnd, CurrentStart, MINUTE )
VAR Result =
    DIVIDE ( TimeDifference, 60, " " )
RETURN
    Result

[Syndicate_Admin]

The same thing continues to happen to me. I need that without the mesasure of INdateRange I keep the records whose time is White, I always mean the first record of the table will be white since it will be the first order depending on the selected dates.

With the InRangeDate measure

without the InRangeDate measure

I would need the final solution to come out like this

I hope I have explained, I am waiting

Thank you very much for your help

[tamerj1]

Hi @Syndicate_Admin , @RWRW 
I hope the following solves the problem

Time Measure =
// Measure to calculate the time difference between the completion of one batch and the beginning of the next batch
VAR SelectedTable =
    FILTER (
        ALLSELECTED ( Pmx[Start Date], Pmx[End Date] ),
        Pmx[End Date] <> BLANK ()
    )
VAR CurrentStart =
    MAX ( Pmx[Start Date] )
VAR PreviousDatesTable =
    FILTER ( SelectedTable, Pmx[Start Date] < CurrentStart )
VAR PreviousEnd =
    MAXX ( PreviousDatesTable, Pmx[End Date] )
VAR FirstSelectedDate =
    MINX ( PreviousDatesTable, Pmx[End Date] )
VAR TimeDifference =
    DATEDIFF ( PreviousEnd, CurrentStart, MINUTE )
VAR Result =
    DIVIDE ( TimeDifference, 60, " " )
RETURN
    IF ( PreviousEnd = FirstSelectedDate, " ", Result )

[Syndicate_Admin]

I refer to this line

VAR PreviousEnd = MAXX ( FILTER ( SelectedTable, Orders[Start Date] < CurrentStart ), Orders[End Date] )

Rename in my code the table and the fields but it is still the same.

Thank you

[tamerj1]

@Syndicate_Admin  @RWRW 
Which column that has the blank values?

[tamerj1]

Hi @Syndicate_Admin 
Here is a sample file with the solution https://www.dropbox.com/t/xt1mFlBtHgSNF5Ur

Time = 
VAR CurrentStart= Orders[Start Date]
VAR PreviousEnd = MAXX ( FILTER ( Orders, Orders[End Date] < CurrentStart ), Orders[End Date] )
VAR TimeDifference = DATEDIFF ( PreviousEnd, CurrentStart, MINUTE )
RETURN
    IF ( NOT ISBLANK ( PreviousEnd ), DIVIDE ( TimeDifference, 60 ) )