DAX Add Custom Column, Date Duration minus WeekEnds

[PeteD1302]

I have a DAX column in my table as follows; 

= Table.AddColumn(#"Inserted Merged Column1", "Project Cycle Time", each Duration.TotalDays ([DateCompleted] - [DateStart]))

I have a Date table that contains the date range, with a column for ISWorkDay (1 for true, 0 for false).

I want to be able to amend my DAX custom column to remove the weekend days.

Appreciate any help.

[v-alq-msft]

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.

[PeteD1302]

Hi, I have tried the suggestion from Employee..Microsoft, created new query, sample dates (as below), and resulting error message.

[PeteD1302]

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.6

SLA 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]
        )
    )

[PeteD1302]

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

[v-alq-msft]

Hi, @PeteD1302 

Here is the pbix file . Hope it helps.

Best Regards

Allan

[PeteD1302]

Hi Alan (at Microsoft), Thanks for the sample file, that helped a lot, one step closer to a solution.
I have tested with 2 sets of date formatted data, one with Date [works OK], the other with Date/Time; this one has an error message.
I really need my result in part days, not whole days.
Do you have any suggestions.

[v-alq-msft]

Hi, @PeteD1302 

I'd like to suggest you use datetime data type as the parameters. You need to use List.DateTimes function instead of List.Date.

Best Regards

Allan

[PeteD1302]

Hi Allan, I did change to Date Time formats. For both Date and Date-Time the result appears to be the same; all full days.
I tried changing to Decimal and display 1 decimal point, both have e.g. 97.0
Attached screen print for the Date-Time below.

[PeteD1302 · Accepted solution]

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

[v-alq-msft]

Hi, @PeteD1302 

Based on your description, I'd like to suggest you refer to the following thread. You may manage to do it step by step.

How to calculate net working days - Step by Step 

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

Check this file having datediff excluding weekend

https://www.dropbox.com/s/y47ah38sr157l7t/Order_delivery_date_diff.pbix?dl=0

Appreciate your Kudos.

[PeteD1302]

Hi, I was wanting a query editor solution, if possible; this currently gives me a day difference (including weekends) as e.g. 8.6 days, minus weekends would be = 6.6 days.

I did look at the Order_delivery_date_diff tables and the calculation for Way 4 date Diff, this is similar to a calculated column I am already using giving a final result in whole days. My start and end dates are in Date/Time, I was wanting to achieve a day result in part days e.g.; 6.6. My final data table has many rows and I am calcuating an average for a large dataset, so the number of days needs to be accurate in part days.

Thanks

[ImkeF]

Hi,

for those readers who don't have that additional column:

I just published a NETWORKDAY-function here that doesn't even require that additional column: https://www.thebiccountant.com/2020/02/20/date-networkdays-function-for-power-query-and-power-bi/ 

[Greg_Deckler]

@ImkeF might have a solution. Otherwise, see if my Net Work Days quick measure might be a solution. https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/td-p/367362