Forum Discussion

PeteD1302's avatar
PeteD1302
Helper I
6 years ago
Solved

DAX Add Custom Column, Date Duration minus WeekEnds

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.

Need Help
  • PeteD1302's avatar
    PeteD1302
    6 years ago

    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

14 Replies

    • PeteD1302's avatar
      PeteD1302
      Helper I
      6 years ago

      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

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    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's avatar
      PeteD1302
      Helper I
      6 years ago

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

       

       

       

       

      • PeteD1302's avatar
        PeteD1302
        Helper I
        6 years ago

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