Forum Discussion
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...
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"
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 use also my Power Query solution. If you don't know how - read note below my post.