Forum Discussion
DAX Add Custom Column, Date Duration minus WeekEnds
Note that I have managed to find a solution from a previous post; NETWORKDAYS in PowerQuery Editor [Super User IV, Mariusz], from the downloaded example file; duration with no weekends and holidays.pbix
community.powerbi.com/t5/Power-Query/NETWORKDAYS-in-PowerQuery-Editor/td-p/940950
Hi, PeteD1302
Based on your description, I created data to reproduce your scenario.
Table:
You may create a new Query named 'Function' as follows.
(StartDate as date,EndDate as date) as number =>
let
Source = List.Dates,
#"Invoked FunctionSource" = Source(StartDate,Duration.Days(EndDate-StartDate)+1,Duration.From(1)),
#"Converted to Table" = Table.FromList(#"Invoked FunctionSource", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
Custom1 = Table.AddColumn(#"Converted to Table","DayOfWeek",each Date.DayOfWeek([Column1],Day.Sunday)+1,type number),
#"Filtered Rows" = Table.SelectRows(Custom1, each ([DayOfWeek] <> 1 and [DayOfWeek] <> 7)),
Custom2 = Table.RowCount(#"Filtered Rows")
in
Custom2
Then you can add a custom column in 'Table' as below.
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, I have tried the suggestion from Employee..Microsoft, created new query, sample dates (as below), and resulting error message.
Note that my table calculated column that I am using that gives whole days is as below.
If I change the data type to decimal it just gives e.g. 10.0 My aim is to achieve days as e.g. 9.6SLA Days = VAR Created =
MAX (Table[StartDate])
VAR Closed =
MAX (Table[EndDate])
RETURN
CALCULATE (
SUM ( 'Date'[IsWorkDay] ),
FILTER (
ALL ( 'Date'[Date] ),
'Date'[Date] >= [StartDate]
&& 'Date'[Date] <= [Created]
)
)Hi Allan (Microsoft), Are you able to attach a sample scenario pbix file, so I can see where I am going wrong.
I followed your instrucions exactly, and cannot see why I am getting an error.
Thanks Peter