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 want to create a measure that will display the number of working days it took for uploading a certain document. For a KPI dashboard I have a measure that a certain measure is uploaded before the 10th working day that month.
My source data looks like this:
I have a date table tha talso has the working days included:
Ideally I woudl now get a measure that takes the last date for a certain process (e.g. 16 january in top example) and calculates how many working days it's been since the beginning of that month, i.e. the 16th is the 11th working day.
I was thinking of something like adding a column "Number workday" or something, that says which working day the date is, but I'm not sure how this would work or if it is the best/most elegant solution.
I would love to hear if this is possible and if so how,
Thanks anyway for any tips and tricks
Solved! Go to Solution.
HI @Olaf18,
I'd like to suggest you take a look at the dax NETWORKDAYS function, it can be used to calculate te working day and auto exclude the weekend and holidays range calculations based on parameters.
Calculate Workdays Between Two Dates in Power BI (enterprisedna.co)
NETWORKDAYS function (DAX) - DAX | Microsoft Learn
Regards,
Xiaoxin Sheng
hi @Olaf18
you would get a solution quicker if you paste your sample data as text and also provide table showing the expected result.
hi @FreemanZ, thanks for the Tip:
Import DateProcess
Here a sample of the [Process] Table
Date | Process |
17/01/2023 | Model uploads |
17/01/2023 | Dates table |
13/01/2023 | Perform Checks |
10/01/2023 | Manager meeting |
06/01/2023 | Uploads part 2 |
02/01/2023 | Uploads part 1 |
15/12/2022 | Model uploads |
15/12/2022 | Dates table |
14/12/2022 | Perform Checks |
13/12/2022 | Manager meeting |
12/12/2022 | Uploads part 2 |
09/12/2022 | Uploads part 1 |
My initial idea is to add a "Working date number" column in this table, that way I can perhaps make a measure to see what the corresponding working day is, this would look like this:
Date | Process | Working day num |
17/01/2023 | Model uploads | 12 |
17/01/2023 | Dates table | 12 |
13/01/2023 | Perform Checks | 10 |
10/01/2023 | Manager meeting | 7 |
06/01/2023 | Uploads part 2 | 5 |
02/01/2023 | Uploads part 1 | 1 |
15/12/2022 | Model uploads | 11 |
15/12/2022 | Dates table | 11 |
14/12/2022 | Perform Checks | 10 |
13/12/2022 | Manager meeting | 9 |
12/12/2022 | Uploads part 2 | 8 |
09/12/2022 | Uploads part 1 | 7 |
However, perhaps it might be easier to make a measure directly, without creating an additional column?
For the dashboard I need to know whether the "Model uploads" happened before or after the 11th working day for example.
I hope this provides some additional information.
HI @Olaf18,
I'd like to suggest you take a look at the dax NETWORKDAYS function, it can be used to calculate te working day and auto exclude the weekend and holidays range calculations based on parameters.
Calculate Workdays Between Two Dates in Power BI (enterprisedna.co)
NETWORKDAYS function (DAX) - DAX | Microsoft Learn
Regards,
Xiaoxin Sheng
@v-shex-msft Thanks! this worked perfectly.
I added a column "first of month" to the first table in power query using "STARTOFMONTH"
and then added the custom column using Calculate Workdays Between Two Dates in Power BI (enterprisedna.co)!
The idea is that "model uploads" happens on the 17th of january. This is the 12th working day in the month, hence the working day of completion is 12. I could then see if the Model uploads happen before the 11ht working day for example (which in this case it doesn't).
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 |
---|---|
39 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
44 | |
32 | |
30 | |
18 | |
17 |