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 Days & Hours 

 

String NewColumn =
var vHours=ROUND([Columnwithhours],0)
var vDays=int( vHours/24)
var vRemainingHours=MOD(vHours, 24)
return
vDays&" Days & "& vRemainingHours& " Hours"
 
So the final output is something that looks like......5 days 4 hours.  
 
However, I need to report with this 'NewColumn' containing days & hours at a monthly level, Average time worked per month.  When I include the new column in my table it reports per ID per month, something like this
 
 Hours workedDays & Hours
Jan120 days 12 hours
Jan220 days 22 hours
Jan100 days 10 hours
 
 
The final output I require would be 
 
 Ave hours workedDays & Hours
Jan482 days 0 hours
Feb552 days 7 hours
Mar502 days 2 hours

 

Any help would be much appreciated 

Need Help

3 Replies

    • KimNixon's avatar
      KimNixon
      Frequent Visitor
      6 years ago

      Thank you for the steer, I am getting a circular reference error, I must be going wrong somewhere

  • 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