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

[JustinDoh1]

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.

[MasonMA · Accepted solution]

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

[xifeng_L · Accepted solution]

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~

[JustinDoh1]

@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!

[MasonMA · Accepted solution]

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