Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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 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.
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
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |