Dynamic networkdays function in measure

[TheEdwardFancy]

Hi all!

I'm trying to build a measure that allows me to work out the networkdays in a measure.

I have the summary data below:

Item	Package	Start Date	End Date
Oranges	One	02/02/2022	06/08/2022
Oranges	Two	15/04/2022	16/06/2022
Apples	One	01/03/2022	16/06/2022
Bananas	One	05/04/2022	03/10/2022

I have a date table so that I can put the months of 2022 in the column headers in a matrix. I want Item in my rows. For my values, I want the total of working days of each month to get:

	Jan	Feb	Mar	Apr	May	Jun	Jul
Apples	0	0	23	21	22	22	4
Oranges	0	19	23	32	44	34	21
Pears	0	0	0	19	22	22	21

I'm circling around an answer using sumx, networkdays, and the min and max of dates, but my table only returns the correct answer when I add 'Package' to the hierarchy and expand it. Can anyone point me in the right direction please?

 Very much appreciated

[Greg_Deckler]

@TheEdwardFancy You'll have to get the MIN start date and MAX end date and then calculate the number of working days, something like:

Measure =
  VAR __Min = MIN('Table'[Start Date])
  VAR __Max = MAX('Table'[End Date])
  VAR __Result = NETWORKDAYS(__Min, __Max)
RETURN
  __Result

You may also need to combine this with something like Time Intervals:

Take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

Not sure what your current calculation looks like.

[TheEdwardFancy · Accepted solution]

I used a workaround by using countrows on my data table and filtering out weekends. SUMX then calculated it by item and totalled the answer

[TheEdwardFancy]

Thanks for this @Greg_Deckler. This is my current measure at the moment:

VAR StartMonth = EOMONTH(max('DateTable'[Month]),-1)+1

VAR EndMonth = max('DateTable'[Month])

RETURN calculate(

                sumx('Data',

                    if(

                        or  (max('Data'[Start Date])>= EndMonth,

                            max('Data'[End Date])<=StartMonth),0,

                        NETWORKDAYS(

                            max(StartMonth,max('Data'[Start Date])),

                            min(EndMonth,max('Data'[End Date]))

                            )

                        )

                    )

                )

Which gives me the results in my OP. My problem is that my dates span across months, so I can't just have a standard networkday calculation with the start and end dates from the columns. Apologies if I'm not explaining myself properly, but I appreciate your reply!