Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
4 years ago

I calculate in hours between two different record dates

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 a...
Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
4 years ago

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's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
4 years ago

Good morning

Any suggestions?

Thank you and greetings

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      Syndicate_Admin
      Icon for Administrator rankAdministrator
      4 years ago

      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's avatar
        Syndicate_Admin
        Icon for Administrator rankAdministrator
        4 years ago

        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