Excel Formula to PowerBI Formula (Dates/Network days)

[PowerBinewbie19]

Hi there!

I'm trying to take the following excel formulas and convert them into PowerBI formulas within Power Query Editor. I have created 4 columns in Power Query Editor which will store the following 4 formulas and should generate the results for each row and column. To clarify, all the fields I do have so that is not a problem. Please see Excel formulas below that I want to change into PowerBI formulas, needed to be inserted into the space below (in screenshot):

1. =IF([@[Start Date]],NETWORKDAYS([@[Date reported]],[@[Start Date]]),"")

2. =IF([@[Completed Date]],NETWORKDAYS([@[Date reported]],[@[Completed Date]]),"WIP")

3. =VLOOKUP([@[Request Type]],'SLA baseline'!A:B,2,FALSE)

4. =IF(ISBLANK([@[Completed Date]]),TODAY()-[@[Date reported]],"DONE")

5. =IF(ISBLANK([@[Completed Date]]),"WIP",IF([@[SLA baseline]]>=[@[Resolution Time (Days)]],"Yes","No"))

**How do I change "NETWORKDAYS" into a PowerBI formula**

My Goal: I'm trying to take manual excel formulas and automate them through PowerBI so monthly, we can receive the correct information. 

[PowerBinewbie19 · Accepted solution]

My solution was just to watch youtube videos to help learn about the formulas. Understand the formulas and it helps gain a better understanding. Watch the videos below to help:
https://www.youtube.com/watch?v=e2ic432NvhY
https://www.youtube.com/watch?v=I_yIKonqO7o

[dufoq3]

You can count working days between 2 dates also this way:

Used sample holidays (you can refer your Holidays table or edit 3rd step Holidays in my query)

Result: Added number or working days between 2 dates (weekends and holidays excluded)

1st step Source = Table.FromRows(Json.Document... --> this is just sample data.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUBSIjAyMTJR0lA3M4J1YnWsnQRNfAEiZnbKBriCxnoWtoBJczhHNiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Date" = _t, #"End Date" = _t]),
    ChangedTypeGB = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}}, "en-GB"),
    Holidays = #table(type table[Holiday Date = date], {{#date(2024,1,1)}, {#date(2024,1,6)}, {#date(2024,9,15)}, {#date(2024,11,1)}, {#date(2024,11,17)}, {#date(2024,12,24)}, {#date(2024,12,25)}, {#date(2024,12,26)}}),
    StepBack = ChangedTypeGB,
    Ad_WorkingDays = Table.AddColumn(StepBack, "Working Days", each List.Count(List.Select(List.Difference(List.Transform({Number.From([Start Date])..Number.From([End Date])}, Date.From), Holidays[Holiday Date]), each Date.DayOfWeek(_, Day.Monday) < 5)), Int64.Type)
in
    Ad_WorkingDays

[collinsg]

Good day PowerBinewbie19,

I'm not sure if you were asking how to translate all the formulae or if it was specifically NETWORKDAYS you needed an answer on. My reply is specifically on NETWORKSDAYS. If you wish for an equivalent of Excel NETWORKDAYS in Power Query then Imke has a solution here.

Hope this helps