topic Working days since beginning of month in DAX Commands and Tips

Working days since beginning of month

Anonymous — Wed, 18 Jan 2023 13:39:42 GMT

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

Re: Working days since beginning of month

FreemanZ — Wed, 18 Jan 2023 14:42:21 GMT

hi Anonymous

you would get a solution quicker if you paste your sample data as text and also provide table showing the expected result.

Re: Working days since beginning of month

Anonymous — Wed, 18 Jan 2023 15:15:35 GMT

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.

Re: Working days since beginning of month

FreemanZ — Wed, 18 Jan 2023 15:21:38 GMT

hi Anonymous

for the first row:

17/01/2023

Model uploads

could you explain how comes the 12?

Re: Working days since beginning of month

Anonymous — Wed, 18 Jan 2023 15:28:34 GMT

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).

Re: Working days since beginning of month

Anonymous — Tue, 24 Jan 2023 07:23:22 GMT

HI Anonymous,

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

Re: Working days since beginning of month

Anonymous — Tue, 24 Jan 2023 14:54:14 GMT

Anonymous 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)!