Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

BI DAX NETWORKDAYS WITH BLANK VALUES

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 onRelease DtDAY excl WKD
9/08/202412/08/20241
9/08/202412/08/20241
9/08/202413/08/20242
9/08/202414/08/20243
9/08/2024 -32514
9/08/2024 -32514
2 ACCEPTED SOLUTIONS
Vidushi_Mangal
Regular Visitor

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

 

 

View solution in original post

dufoq3
Super User
Super User

Hi @Anonymous,

 

DAX Custom Column solution:

dufoq3_0-1723806529419.png

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

 

Power Query Solution:

dufoq3_1-1723806565738.png

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"

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

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
Super User
Super User

Hi @Anonymous,

 

DAX Custom Column solution:

dufoq3_0-1723806529419.png

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

 

Power Query Solution:

dufoq3_1-1723806565738.png

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"

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Anonymous
Not applicable

@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? 

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


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Vidushi_Mangal
Regular Visitor

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

 

 

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors