Help with DateDiff to calculate a better Year, Month, Day combination

[Adam01]

Afternoon All,

I currently have 3 DAX expression that calculates a date difference from a fixed date, 1 for year, 1 for month, 1 for day that is essentially DATEDIFF ( [Fixed Date], TODAY(), YEAR ) but replace YEAR with MONTH and DAY respectively

Here's what I want: "2 Years, 5 months, 18 days"

Here's what I get: "2 years, 29 months, 899 days"

(Please note these values are plucked randomly from the air they might not be the correct conversion)

I understand why the DAX is returning the numbers I get I am just unsure of how to return the desired value I want

Thanks

[ppm1 · Accepted solution]

Here's one way to do it. Just replace T2 with your actual table name. However, I recommend you do it in a measure instead of a column (so you can do calculations first).

FromToday =
VAR t =
    TODAY ()
VAR dt = T2[Date]
VAR months =
    DATEDIFF ( dt, t, MONTH )
VAR todaymonthsago =
    EDATE ( t, - months )
VAR yrs =
    INT ( months / 12 )
VAR mnths =
    IF ( dt < todaymonthsago, months - 12 * yrs, months - 12 * yrs - 1 )
VAR dys =
    IF (
        dt < todaymonthsago,
        INT ( todaymonthsago - dt ),
        INT ( EDATE ( dt, 1 ) - todaymonthsago )
    )
RETURN
    yrs & " yrs " & mnths & " mos " & dys & " days"

Pat

[Adam01]

Hi @ppm1 

That seems to have worked, thank you very much

[v-yueyunzh-msft]

Hi , @Adam01 

According to your description, you seems to want to get the Years ,Month and Days customly.

This is my test data:

We can create three calcualted columns like this:

Year = var _addyear= EOMONTH([Column1],12*DATEDIFF([Column1],[Column2],YEAR))
return
IF(_addyear>[Column2],DATEDIFF([Column1],[Column2],YEAR)-1,DATEDIFF([Column1],[Column2],YEAR))

Month = var _addyear= EOMONTH([Column1],12*[Year]-1)+1
var _month_day =  IF( MONTH([Column1]) > MONTH( [Column2]) , 1, IF(DAY([Column1])>DAY([Column2]) ,1,-1))
return
IF(_month_day=1,DATEDIFF(_addyear,[Column2],MONTH)-1,DATEDIFF(_addyear,[Column2],MONTH))

Days = var _addyear= EOMONTH([Column1],12*[Year]-1)+1
 var _addmonth =DATE( YEAR( EOMONTH(_addyear,[Month]) ) ,MONTH( EOMONTH(_addyear,[Month]) ) , DAY([Column1]))
 return

DATEDIFF(_addmonth,[Column2],DAY)

Then we can get the result is as follows:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Adam01]

Hello @v-yueyunzh-msft ,

Please see below for a screenshot of what i'm trying to get from this query:

However using your code I very nearly get my desired output except the month / day overlap:

Here's the code I'm using:

Year = 
VAR AddYear =
    EOMONTH (
        Dates[Start Date],
        12 * DATEDIFF ( Dates[Start Date], Dates[Today], YEAR )
    )
RETURN
    IF (
        AddYear > Dates[Today],
        DATEDIFF ( Dates[Start Date], Dates[Today], YEAR ) - 1,
        DATEDIFF ( Dates[Start Date], Dates[Today], YEAR )
    )

Month = 
VAR AddYear =
    EOMONTH ( Dates[Start Date], 12 * [Year] - 1 ) + 1
VAR MonthDay =
    IF (
        MONTH ( Dates[Start Date] ) > MONTH ( Dates[Today] ),
        1,
        IF ( DAY ( Dates[Start Date] ) > DAY ( Dates[Today] ), 1, -1 )
    )
RETURN
    IF (
        MonthDay = 1,
        DATEDIFF ( AddYear, Dates[Today], MONTH ) - 1,
        DATEDIFF ( AddYear, Dates[Today], MONTH )
    )

Day = 
VAR AddYear =
    EOMONTH ( Dates[Start Date], 12 * Dates[Year] - 1 ) + 1
VAR AddMonth =
    DATE ( YEAR ( EOMONTH ( AddYear, Dates[Month] ) ), MONTH ( EOMONTH ( AddYear, Dates[Month] ) ), DAY ( Dates[Start Date] ) )
RETURN
    DATEDIFF ( AddMonth, Dates[Today], DAY )

[eliasayyy]

can you hsare some sample please