Forum Discussion

KimNixon's avatar
KimNixon
Frequent Visitor
6 years ago

String Days and Hours, monthly

Hi,   I have a column which reports time worked in hours (per id/row).  This I can report on monthly and I can calculate monthly average.  However I am converting the hours using DAX to display Day...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

HI KimNixon ,

You can try to use the following measure formula to calculate the average of work hour based on month and convert them to time duration string:

Measure =
VAR _avg =
    CALCULATE ( AVERAGE ( 'Table'[Hour] ), VALUES ( 'Table'[Date].[MonthNo] ) )
VAR _rand =
    ROUNDDOWN ( _avg, 0 )
RETURN
    IF (
        _avg >= 24,
        INT ( _rand / 24 ) & " day"
            & MOD ( _rand, 24 ) & " hours",
        _rand & " hours"
    )

Regards,

Xiaoxin Sheng