topic Re: Help with DateDiff to calculate a better Year, Month, Day combination in DAX Commands and Tips

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

Adam01 — Fri, 05 May 2023 14:07:46 GMT

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

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

eliasayyy — Fri, 05 May 2023 14:13:16 GMT

can you hsare some sample please

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

v-yueyunzh-msft — Mon, 08 May 2023 02:47:58 GMT

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

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

Adam01 — Wed, 10 May 2023 08:28:57 GMT

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 )

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

ppm1 — Wed, 10 May 2023 12:43:29 GMT

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

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

Adam01 — Wed, 10 May 2023 14:42:44 GMT

Hi ppm1

That seems to have worked, thank you very much