Forum Discussion
Solved
If then statement with embedded calculation
I feel like I am close, and I can make each column work seperately, but now I need them to work together if possible. Where is my formula going wrong? I have two columns, [Work Start Date] and ...
Hi LFrench , another solution that you could look at. I'll attach the images and the M code used.
Here's the code:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"StartDate", type datetime}, {"EndDate", type datetime}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Date.From([StartDate]) is date then Duration.Days([StartDate] - [EndDate]) + 1
else if [EndDate] = null then Duration.Days(Date.From(DateTime.FixedLocalNow()) - [StartDate]) + 1 else null)
in
#"Added Custom"
= Table.AddColumn("Days in Position",
each Duration.Days(([Work End Date]??Date.From(DateTime.LocalNow())) - [Work Start Date]) +1,
Int64.Type)