Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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 |
---|---|
90 | |
85 | |
65 | |
62 | |
58 |
User | Count |
---|---|
147 | |
113 | |
95 | |
81 | |
71 |