Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
Your input will be much appreciated.
Kind Regards
Mizeel
Solved! Go to 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
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
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.
User | Count |
---|---|
100 | |
87 | |
81 | |
76 | |
71 |
User | Count |
---|---|
113 | |
104 | |
100 | |
73 | |
65 |