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:

 

ItemPackageStart DateEnd Date
OrangesOne02/02/202206/08/2022
OrangesTwo15/04/202216/06/2022
ApplesOne01/03/202216/06/2022
BananasOne05/04/202203/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:

 

 JanFebMarAprMayJunJul
Apples00232122224
Oranges0192332443421
Pears00019222221

 

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

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

3 Replies

  • 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

      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'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!