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 c...
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

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.