Using Text.Start and Text.End with math operations

[iblock]

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.

[Anonymous · Accepted 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.