The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
Solved! Go to 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
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"
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
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"
@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 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