Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I am trying to make this formula work:
Table.AddColumn(#"Renamed Columns", "Term (Months)", each List.Max((Number.FromText(Text.Start([Exp YYYYMM],4))-Number.FromText(Text.Start([Eff YYYYMM],4)))*12+((Number.FromText(Text.End([Exp YYYYMM],2))-Number.FromText(Text.End([Eff YYYYMM],2)))),1))
The Eff YYYYMM and Exp YYYYMM columns are of type Integer, so I am not sure if the approach can be simplified.
For example, if the Eff YYYYMM = 201507 and Exp YYYYMM = 201707 then Term (Months) should equal 24.
The final result should be in number format.
Solved! Go to Solution.
Hi @iblock ,
According to your statement, I think [Eff YYYYMM] and [Exp YYYYMM] columns are of type whole number in Power BI. Here I suggest you to try below code to create custom column.
let
_Mod_Exp = Number.Mod([Exp YYYYMM],100),
_Mod_Eff = Number.Mod([Eff YYYYMM],100)
in
(([Exp YYYYMM]-_Mod_Exp)/100
-
([Eff YYYYMM]-_Mod_Eff)/100)*12
+
_Mod_Exp - _Mod_Eff
or
If you need List.Max function, you can try this code.
let
_Mod_Exp = Number.Mod([Exp YYYYMM],100),
_Mod_Eff = Number.Mod([Eff YYYYMM],100)
in
List.Max(
(([Exp YYYYMM]-_Mod_Exp)/100
-
([Eff YYYYMM]-_Mod_Eff)/100)*12
+
_Mod_Exp - _Mod_Eff
,1
)
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @iblock ,
According to your statement, I think [Eff YYYYMM] and [Exp YYYYMM] columns are of type whole number in Power BI. Here I suggest you to try below code to create custom column.
let
_Mod_Exp = Number.Mod([Exp YYYYMM],100),
_Mod_Eff = Number.Mod([Eff YYYYMM],100)
in
(([Exp YYYYMM]-_Mod_Exp)/100
-
([Eff YYYYMM]-_Mod_Eff)/100)*12
+
_Mod_Exp - _Mod_Eff
or
If you need List.Max function, you can try this code.
let
_Mod_Exp = Number.Mod([Exp YYYYMM],100),
_Mod_Eff = Number.Mod([Eff YYYYMM],100)
in
List.Max(
(([Exp YYYYMM]-_Mod_Exp)/100
-
([Eff YYYYMM]-_Mod_Eff)/100)*12
+
_Mod_Exp - _Mod_Eff
,1
)
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
73 | |
70 | |
38 | |
24 | |
23 |
User | Count |
---|---|
96 | |
93 | |
50 | |
41 | |
40 |