BI DAX NETWORKDAYS WITH BLANK VALUES

[Anonymous]

BI DAX NETWORKDAYS WITH BLANK VALUES

Hi There - I'm novice in BI please help me with my formula not workingt to identify the blank values between 2 dates.

Below column example is working well by excluding weekends but when one of the date is blank is showing the negative big value.

My formula is  = NETWORKDAYS('Cancelled STOs'[Created on],'Cancelled STOs'[Release Dt],1)-1))

How to add the condition that if blank to return as "blank" ?

Created on	Release Dt	DAY excl WKD
9/08/2024	12/08/2024	1
9/08/2024	12/08/2024	1
9/08/2024	13/08/2024	2
9/08/2024	14/08/2024	3
9/08/2024		-32514
9/08/2024		-32514

[Vidushi_Mangal · Accepted solution]

You can convert all  the blank column to 0 before applying this formula. Then it will return 0 rather than any negative value. for that purpose goto :

Power Query--> Transform-->Replace-->put blank in find tab and o in replace tab-->OK

[Anonymous]

thank you @Vidushi_Mangal It's great knowing this option but I couldn't mix up the "0" values I have with the "blank" lines

[dufoq3 · Accepted solution]

Hi @Anonymous,

DAX Custom Column solution:

DAY excl WKD = IF(NOT(ISBLANK([Release Dt])),NETWORKDAYS([Created on], [Release Dt]) -1)

Power Query Solution:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WstQ3sNA3MjAyUdJRMjSCc2J1iJcyxi1lglNKAZtILAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Created on" = _t, #"Release Dt" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Created on", type date}, {"Release Dt", type date}}),
    #"Added Custom" = Table.AddColumn(ChangedType, "DAY excl WKD", each 
        [ a = List.Dates([Created on], Duration.TotalDays([Release Dt]-[Created on]) +1, #duration(1,0,0,0)),
          b = try List.Count(List.Select(a, (x)=> not List.Contains({5, 6}, Date.DayOfWeek(x, Day.Monday)))) -1 otherwise null
        ][b], Int64.Type)
in
    #"Added Custom"

[Anonymous]

@dufoq3  I used the DAX formula which worked well. I'm unsure the huge formula you shared for Query is something I'd copy and paste the way you sent to me and just change the "created on" and " release date" by picking from my file? 

[dufoq3]

You can use also my Power Query solution. If you don't know how - read note below my post.

[Vidushi_Mangal · Accepted solution]

You can convert all  the blank column to 0 before applying this formula. Then it will return 0 rather than any negative value. for that purpose goto :

Power Query--> Transform-->Replace-->put blank in find tab and o in replace tab-->OK