Business Days Elapsed for each month.

[gus_jr]

Hi everyone, i'm looking for a measure that calculate the quantity of business days of each month.

For example, if today was April 17th, the result were 12 days. Otherwise, for the closed months (past months) that calculates entire business days in that month. The table below, may can reflect what I say. THANKS A LOT!!

Today si Apr 17th
	Business Days Elapsed (Mon to Fri)
Jan	23
Feb	21
Mar	21
Apr	17
May	0
Jun	0
Jul	0
Aug	0
Sep	0
Oct	0
Nov	0
Dec	0

[parry2k]

@gus_jr still not sure what the issue is, if you see image below, total kgs sum is 16292 for mayo and there are 7 days, so 16292 / 7 = 2327 and that is what it is showing.

Either you provide the number that you are expecting or double check it manually

[parry2k]

@gus_jr what is your expected output based on sample pbix data? Not clear what the exact problem is?

[gus_jr]

hi Parry, thanks again. Take a look at the current month. Usually, specifically in this business,  the average daily sales get a pretty uniform behavior month by month. What the example shows us (I think), is that in May, the sales are divided by the total of workdays in that month, instead the six or seven days elapsed until now.
So, look at the "kgs" column, every month is very similar between they. The dayli "kgs" measure, is day by day very regular.

[parry2k]

@gus_jr not sure where the issue is - in your pbix you already calculating days correctly or I am missing something:

[gus_jr]

Hi Parry! thank for you answer! yes, you r right, but what i want is that calculate, for example, the sum of sales or tons or whatever, divided the elapsed days. If was a closed month, divide by the total of day of that month closed, and if is in the current month, divide that sum of sales, tons or whathever, by the elapsed days until today or yesterday. I hope my explication have been clear for you!. Thanks again for your efforts.

[DataNinja777 · Accepted solution]

Hi @gus_jr ,

You can generate your required output in many ways and one example is as follows:

Create a calendar table which identifies weekdays and weekends.  (Please note that this is not identifying national holidays and bank holidays). 

You can create a measure of the countrows of the calendar table, keepfiltering weekdays and days on and before today. 

I attach the pbix file as an example.  

[gus_jr]

Mon to Fri	Jan	Feb	Mar	Apr	May
Tons	1200	1300	1400	1500	1600
Mon to Fri days until today	23	21	21	22	5
AVERAGE DAILY	52.2	61.9	66.7	68.2	320
Mon to Sat	Jan	Feb	Mar	Apr	May
Tons	1200	1300	1400	1500	1600
Mon to Sat days until today	27	25	26	26	6
AVERAGE DAILY	44.4	52	53.8	57.7	267
Mon to Sun	Jan	Feb	Mar	Apr	May
Tons	1200	1300	1400	1500	1600
Mon to Sun days until today	31	29	31	30	7
AVERAGE DAILY	38.7	44.8	45.2	50	229

[gus_jr]

Thank a lot for your support, it was really usefull, but i have to say, that my doubt wasnt pretty clear. What i need is a measure, called Average Daily, that calculate a divide bewtween, for example, the entire sales volume and the days. If it s a closed month, divide for the total workdays, and if its a curren month, divide for the elapsed days of the acutal month. Here i share a table, maybe help you to understand this better, and a little bit of the dashboard that i been working.

[Ashish_Mathur]

Hi,

Share some data to work with.

[gus_jr]

Hi, Ashish, thank you for answer. I shared after that first post, a table and screenshoot of that part of the dashboards, on the messages belows.

[Ashish_Mathur]

You are welcome.  That shows the expected result.  It is not the raw data.

[gus_jr]

https://1drv.ms/u/s!AiDxBZlceu5dxXqpx5L_RPHy2UOd?e=NBfE4F 

[Ashish_Mathur]

I do not know what result you want.  This is what i get.

[gus_jr]

thank you, again. Here I share you a PBX file, that try to show the calculate that I looking for. If you realize that in the current month, has been pass 6 days, and I need that the current month has been divide for that 6 days. In the sample that I shared, the sum of tons is divide por the total of workdays of this current month. Thank you, for try help me. 

[parry2k]

@gus_jr you can easily add a column using NETOWRKDAYS dax function, read more here NETWORKDAYS function (DAX) - DAX | Microsoft Learn