how to get week and day from the number of days

[Anonymous]

Hi all - I was wondering how to show 30 days as "4 weeks and 2 days", instead of 4.28 weeks etc

Does anyone know how to do this?

Thanks in advance!

Dan UK

[jdbuchanan71 · Accepted solution]

@Anonymous 

You can put the AVERAGE inside and INT to get just the whole number to us.

Weeks/Days Avg = 
VAR _TotalDays = INT ( AVERAGE ( 'Your Table'[Days] ) )
VAR _Weeks = INT ( DIVIDE ( _TotalDays, 7 ) )
VAR _Days = MOD ( _TotalDays, 7 )
VAR _WeekString = IF ( _Weeks = 0, BLANK(), _Weeks & IF ( _Weeks = 1, " week", " weeks" ) )
VAR _DayString = _Days & IF ( _Days = 1, " day", " days" )

RETURN _WeekString & " " & _DayString

[Anonymous]

Thank you - thats very very helpful 🙂

[jdbuchanan71]

@Anonymous 

Try something like this.

Weeks/Days = 
VAR _TotalDays = SUM ('Your Table'[Days] )
VAR _Weeks = INT ( DIVIDE ( _TotalDays, 7 ) )
VAR _Days = MOD ( _TotalDays, 7 )
VAR _WeekString = IF ( _Weeks = 0, BLANK(), _Weeks & IF ( _Weeks = 1, " week", " weeks" ) )
VAR _DayString = _Days & IF ( _Days = 1, " day", " days" )

RETURN _WeekString & " " & _DayString

[Anonymous]

Thank you - that is incredibly helpful and will please the management team! How do I do a similar thing to show the average for all them? ie average of all indexs is 20 weeks 2 days etc

for example if I use AVERAGE instead of SUM I get "20 weeks 2.1253 days", how do I round that up to show it as "20 weeks 2 days"?

Thanks again!