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

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's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

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'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