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...
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
    )
)
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.