cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Sum between Range

I have a table called Raw_table that is like the one below, and want to calculate the days between the two excluding weekends. 

Start DateDue Date
1-1-202013-1-2020
6-1-202012-1-2020

 

I created another table using the Calender auto function and numbered the days with 1 for weekdays and 0 for weekends. I then tried to use a query like this.

 

 

 

WorkDays = CALCULATE(SUM('Calendar'[Weekday],FILTER('Calendar',(DATESBETWEEN(Raw_table[Start Date],RawTable[Start Date],RawTable[Due Date])))

 

 

 

This fails but there must be a way of doing it? I have found a number of resources on the web for doing something like the excel NETWORKDAYS but cant get them to work. is there a way using what i have to get it to work 

4 REPLIES 4
Highlighted
Resident Rockstar
Resident Rockstar

Hi, @jamesmh95 , you may want to try this measure,

NetWorkdays Measure = 
COUNTROWS (
    FILTER (
        DATESBETWEEN (
            'Calendar'[Date],
            MIN ( Raw_table[Start Date] ),
            MIN ( Raw_table[Due Date] )
        ),
        WEEKDAY ( 'Calendar'[Date], 2 ) < 6
    )
)

Screenshot 2020-10-27 210538.png

In addition, here's a Power Query solution

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ11DUyMDJQ0lEyNIaxY3WilcyQJIzgErEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Date" = _t, #"Due Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"Due Date", type date}}, "fr"),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "NetWorkdays", each 
        List.Accumulate(
            {0..Duration.Days([Due Date]-[Start Date])},
            0,
            (s,c) => if Date.DayOfWeek(Date.AddDays([Start Date], c), Day.Monday)>4 then s else s+1
        )
    )
in
    #"Added Custom"

Screenshot 2020-10-27 210928.png

Highlighted

Is there a way to get the measure as a column? As I would like to be able to return the average of the number of days? 

Also how would i implement the power query i am new to those and as such dont know how i would implement it with my tables rather than the Json?

Highlighted

DAX formula for calculated column

NetWorkdays CC = 
COUNTROWS (
    FILTER (
        DATESBETWEEN (
            'Calendar'[Date],
            Raw_table[Start Date],
            Raw_table[Due Date]
        ),
        WEEKDAY ( 'Calendar'[Date], 2 ) < 6
    )
)

Screenshot 2020-10-28 130552.png

Highlighted

thank you but when i try i get the following error:
A circular dependency was detected: Raw_table[Column 2], Calendar[Date], Calendar[Calendar-0ae25540-d45c-4ff4-ac09-cd6af6c3ca4a], 13a0b372-8077-49fa-9674-c2b965903db3, Raw_New_Layout_[Task Name], Raw_New_Layout_[Raw_New_Layout_-f197bdcd-f316-4285-9ba6-931a37a888d8], Raw_table[Column 2].

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors