Show time remaining to [CompletionDate] in Months, Weeks and Days

[peterg0417]

Either I'm not googling properly, or the answer just doesn't seem to exist but I have a requirement from a business users to show how much time is left until a project completion date like this:

26 months, 3 weeks, 5 days.

Then once we get within a month of completion it should only show 3 weeks and 6 days, then 3 weeks and 5 days, etc.

DATEDIF in Excel seems capable of this, but I'd prefer to do it in Power BI/DAX, but DATEDIFF in DAX can't as far as I know.

Thanks

[ppm1 · Accepted solution]

Here's one way to do it.

Remaining =
VAR due =
    MIN ( Projects[DueDate] )
VAR adj =
    IF ( DAY ( due ) < DAY ( TODAY () ), -1, 0 )
VAR months =
    DATEDIFF ( TODAY (), due, MONTH )
VAR adjdue =
    EDATE ( TODAY (), months + adj )
VAR remainingdays =
    INT ( due - adjdue )
VAR weeks =
    INT ( remainingdays / 7 )
VAR days =
    MOD ( remainingdays, 7 )
VAR monthstext =
    IF ( months + adj = 0, "", months + adj & " months " )
VAR weekstext =
    IF ( weeks = 0, "", weeks & " weeks " )
VAR daystext =
    IF ( days = 0, "", days & " days" )
RETURN
    monthstext & weekstext & daystext

Pat