Forum Discussion

TheEdwardFancy's avatar
TheEdwardFancy
New Member
3 years ago
Solved

Dynamic networkdays function in measure

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/20...
Need Help
  • TheEdwardFancy's avatar
    TheEdwardFancy
    3 years ago

    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

Greg_Deckler's avatar
Greg_Deckler
Community Champion
3 years ago

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's avatar
TheEdwardFancy
New Member
3 years ago

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!