Passing range to DATESBETWEEN Power BI

[Anonymous]

I've been reading various threads and guides to performing a 'NETWORKDAYS' style calculation in Power BI but struggling to make it work.

I have a table like this:

Team |  Meeting    |  Report
aaa  | 1/1/2018    |  9/1/2018
aaa  | 1/1/2018    |  7/1/2018
bbb  | 1/1/2018    |  1/2/2018
bbb  | 1/1/2018    | 
ccc  | 1/1/2018    |  3/3/2018
aaa  | 1/1/2018    | 

And I want to return the average days without weekends and holidays, something like this:

Team | average
aaa  | 5 (10/2)
bbb  | 23 (45/1)
ccc  | 45 (45/1)

I have this non-working function:

Ave. weekdays to report = CALCULATE(AVERAGEX(DateTable[Weekday]),
                                    DATESBETWEEN(DateTable[Date],
        Planning[Meeting],Planning[Meeting]))

Where DateTable is:

Date    | Weekday
5/1/2018| 1
6/1/2018| 0
7/1/2018| 0

and so on...

My first problem is passing a table column to DATESBETWEEN returns an error. Other ideas I had were a SUMX looking at RELATEDTABLE but again, syntax is tripping me up.

Essentially, I want to iterate over Planning and count the dates between Meeting and Report from the Dates table, filtered by Weekday = 1.

[v-yuezhe-msft]

@Anonymous,

Firstly, create a calculated column in your  table.

NetWorkingDays = 
IF (
    NOT(ISBLANK (Planning[Report])),
        CALCULATE (
            sum ( 'Date'[Weekday] ),
            DATESBETWEEN ( 'Date'[Date], Planning[Meeting], Planning[Report])
        ))

Secondly, create the following measures in your table.

Team number = CALCULATE(COUNTA(Planning[Team]),FILTER(Planning,NOT(ISBLANK(Planning[Report]))))

Average workingday = SUM(Planning[NetWorkingDays])/[Team number]


 

Regards,
Lydia 

[Anonymous]

---

@v-yuezhe-msftwrote:

@Anonymous,

Firstly, create a calculated column in your  table.

NetWorkingDays = 
IF (
    NOT(ISBLANK (Planning[Report])),
        CALCULATE (
            sum ( 'Date'[Weekday] ),
            DATESBETWEEN ( 'Date'[Date], Planning[Meeting], Planning[Report])
        ))

---

@v-yuezhe-msft Thanks Lydia  but I'd rather not go down the calculated column route as I have a few of these to do and want to work with measures for practice also.

[Greg_Deckler]

The first parameter should be a Date/Time column.

https://msdn.microsoft.com/en-us/library/ee634557.aspx

Not sure why it would be generating an error unless it really isn't a Date/Time column?

[Anonymous]

---

@Greg_Decklerwrote:

The first parameter should be a Date/Time column.

https://msdn.microsoft.com/en-us/library/ee634557.aspx

Not sure why it would be generating an error unless it really isn't a Date/Time column?

---

@Greg_DecklerI think that was the problem with that particular part - thanks

[Greg_Deckler]

Did you look at my Quick Measure for NETWORKDAYS?

https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362

[Anonymous]

---

@Greg_Decklerwrote:

Did you look at my Quick Measure for NETWORKDAYS?

https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362

---

I hadn't before now. Thanks.

Is VAR simply a way of creating measures for use in the same function, rather than having two or three separate measures, which you then use in a separate function?

Also one other thing, I don't understand the results in the NetworkDaysHoursMinutes column - just 31hours?

[Greg_Deckler · Accepted solution]

VAR is a way of creating variables within a DAX measure. So, kind of what you are saying but really just think of them as variables just like in any other coding language.

The result (31) is the number of days, not hours.