Forum Discussion

Lucky_BI's avatar
Lucky_BI
Frequent Visitor
3 years ago
Solved

Calculate time by day

 

How do I add up the duration (Time Check out - Time Check in) of each name based on the date of the day excluding him from a different outlet with DAX?

Example:

  • Name : Mike
  • Dates:
    • 20/03/2023
      • Outlet A: 17:00:00 - 08:00:00 = 09:00:00
      • Outlet B: 16:00:00 - 08:00:00 = 08:00:00
    • 21/03/2023
      • Outlet C: 16:30:00 - 08:00:00 = 08:30:00

So, Mike's Duration for 20/03/2023 is 17:00:00 Hours. Meanwhile, Mike's duration for 21/03/2023 is 08:30:00 Hours
Download file: Time.xlsx
Thank you for your help

DAX
Power BI Desktop
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi Lucky_BI 
    DateTime format cannot display times more than 24 hours. This has to be a decimal number or custom formatted string. Please refer to attached sample file with the proposed solution

    Total Hours = 
SUMX ( 
    SUMMARIZE ( 
        'Table',
        'Table'[Name],
        'Table'[Date]
    ),
    CONVERT ( 
        SUMX ( 
            CALCULATETABLE ( 'Table' ),
            'Table'[Time Check Out] - 'Table'[Time Check In]
        ),
        DOUBLE
    )
)

4 Replies

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

    Hi Lucky_BI 
    DateTime format cannot display times more than 24 hours. This has to be a decimal number or custom formatted string. Please refer to attached sample file with the proposed solution

    Total Hours = 
SUMX ( 
    SUMMARIZE ( 
        'Table',
        'Table'[Name],
        'Table'[Date]
    ),
    CONVERT ( 
        SUMX ( 
            CALCULATETABLE ( 'Table' ),
            'Table'[Time Check Out] - 'Table'[Time Check In]
        ),
        DOUBLE
    )
)
    • Lucky_BI's avatar
      Lucky_BI
      Frequent Visitor
      3 years ago

      I've found a solution, sir tamerj1 
      Thank you very much for your help

  • Lucky_BI's avatar
    Lucky_BI
    Frequent Visitor
    3 years ago

    Thank you for the suggestion tamerj1 
    But in this case, a worker has no more than 24 hours working time at each outlet for one day. So the total in one day will not be more than 24 hours. If so how is it?

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

      Lucky_BI 
      Yes but if you aggregate the dates for each name it will exceed 24 hours. However you can just delete the CONVERT part from the code but note that the totals will be wrong.