DateDiff Issue with Month Calculation

[MizeelA]

Hi All,

Trying to calculate the month-to-month date difference in Power BI, however, the result is inaccurate for some dates that fall on the last day of the month (Begin - End dates), while it calculates the correct date in Excel. The lines marked in green in Excel, for example, give the correct date as shown below;

In Excel

Formulae:  

=IF(DATEDIF(M7,N7,"m")=0,1,DATEDIF(M7,N7,"m"))

In power bi

Formulae: 

Order_Term Main = IF(DATEDIFF('GP Data'[PAFBeginDate],'GP Data'[PAFEndDate - Copy],MONTH)=0,1,(DATEDIFF('GP Data'[PAFBeginDate],'GP Data'[PAFEndDate - Copy],MONTH)))

Your input will be much appreciated.

Kind Regards

Mizeel

[MizeelA]

Thanks, @johnt75  for the explanation

[v-yiruan-msft · Accepted solution]

Hi @MizeelA ,

You can add a custom column in Power Query Editor just as below screenshot:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dYzBDcAwCAN34R2J2DRNOwvK/msEUPqsxOPMgd1lKnsMu7TkN3nIai5UPpEwwwTnIVkGVNzfk+lIvH6NnbYKqL3hlK0N", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PAFBeginDate = _t, #"PAFEndDate - Copy" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PAFBeginDate", type date}, {"PAFEndDate - Copy", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Order_Term Main", each Number.RoundDown(Number.From(([#"PAFEndDate - Copy"] - [PAFBeginDate])/( 365.25 / 12 )) ,0 ))
in
    #"Added Custom"

Best Regards

[MizeelA]

Thank you for your help @v-yiruan-msft . The solution worked perfectly. 

[johnt75]

DATEDIFF in DAX doesn't have a concept of whole months, it simply counts the number of boundaries crossed between the two dates, so DATEDIFF( DATE(2018, 2, 28), DATE(2018, 3, 1), MONTH) returns 1 because 1 month boundary has been crossed, even though there is only 1 day between the 2 dates.