Forum Discussion

MizeelA's avatar
MizeelA
Helper I
4 years ago
Solved

DateDiff Issue with Month Calculation

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



Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      MizeelA
      Helper I
      4 years ago

      Thank you for your help Anonymous . The solution worked perfectly. 

  • johnt75's avatar
    johnt75
    Super User
    4 years ago

    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.