topic Re: Question about DATEDIFF & more accurate approach to calculate difference of month? in DAX Commands and Tips

Question about DATEDIFF & more accurate approach to calculate difference of month?

JustinDoh1 — Sat, 01 Nov 2025 23:46:35 GMT

I am having an issue with different calculation of month using Excel and PowerBI.

I have following DAX:

DATEDIFF(

FIRSTDATE( 'Table1'[Survey Date] ),

LASTDATE( 'Table2'[Month Year] ),

MONTH

)

First, when I calculate the difference of month betwen 10/1/2025 & 10/28/2022, in Excel, it shows 35 months, but in PowerBI, it shows 36.

So, I ended up subtracting 1 in order to match with Excel.

When I Googled, it says 35 .1 month.

Next challenge is this:

Difference between: 10/1/2025 & 9/1/2022.

In Excel, it says 37 months.

In Google, it says 37 months.

But, using the measure that I have, it says 37 months in PowerBI.

So, I cannot adjust it by subtracting 1 on PowerBI in this case.

Is there better DAX formula that I could use for more accurate calculation of month?

Thanks.

Re: Question about DATEDIFF & more accurate approach to calculate difference of month?

MasonMA — Sun, 02 Nov 2025 00:02:57 GMT

Hi,

As per MS documentation,

https://learn.microsoft.com/en-us/dax/datediff-function-dax , DATEDIFF doesn’t care about completed months, and it counts month boundaries crossed, which means how many times the calendar month changed, not full months. So from 10/28/2022 to 10/1/2025, it crosses month boundaries 36 times, even though it’s only 35 full months elapsed.
That’s why Power BI returns 36.

To match Excel, you can add a condition check in your meausre,

Updated Measure =
VAR StartDate = FIRSTDATE('Table1'[Survey Date])
VAR EndDate = LASTDATE('Table2'[Month Year])
VAR MonthDiff = DATEDIFF(StartDate, EndDate, MONTH)
VAR Adjust =
IF(DAY(EndDate) < DAY(StartDate), 1, 0)
RETURN
MonthDiff - Adjust

Re: Question about DATEDIFF & more accurate approach to calculate difference of month?

xifeng_L — Mon, 03 Nov 2025 03:09:12 GMT

Hi JustinDoh1

You also can try below measure:

DurationMonth = VAR StartDate = dt"2022-10-28" //FIRSTDATE('Table1'[Survey Date]) VAR EndDate = dt"2025-10-1" //LASTDATE('Table2'[Month Year]) VAR MonthNode = SELECTCOLUMNS( GENERATESERIES(0,1000), "MonthNbr",[Value], "Date",EDATE(StartDate,[Value]) ) RETURN MAXX(FILTER(MonthNode,[Date]<=EndDate),[MonthNbr])

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

Re: Question about DATEDIFF & more accurate approach to calculate difference of month?

JustinDoh1 — Mon, 03 Nov 2025 22:24:28 GMT

xifeng_L Thank you so much! Your DAX worked, but I just accepted MasonMA 's as a solution only because he/she responded earlier. Thank you!