Calculate Fiscal Business Days for 2021 (Day Counter)

[Mohanad-Mustafa]

Hello,

I have a fiscal year for 2021 that starts beginning of December 2020 and ends in end of November 2021.

Right now, we are on business day 197 that basically excludes all the weekends and public holidays in Canada.

What I am trying to accomplish is to create a measure that increment the business day every day so my DAX calculation will work well. My DAX measure should work as follows: Sum(Sales) divided by business day multiplies by 250( Total number of business day in 2021 which is static and doesnt change). Sum of sales is calculated automatically for me using the sum function but how can I create a a counter for business days where the daily counter increase the business day every day automitcally so my forumla keeps refreshing every day.

Thank you kindly for your help

Mohanad

[v-janeyg-msft]

Hi, @Mohanad-Mustafa 

I don't know what your weekday and weeknumber are like, but you can try to create a column to distinguish business day:

Like this:

bussinessday =
IF ( table[weeknumber] <> 6 || table[weeknumber] <> 7, 1, 0  )//6,7 depend on your Saturday and Sunday

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,

Community Support Team _ Janey

[Mohanad-Mustafa]

Thanks for your kind reply janeyg, I will give it a try soon and let you know the outcome

[v-janeyg-msft]

@Mohanad-Mustafa  

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If problem still persists, pls let me know.

Best Regards,

Community Support Team _ Janey

[v-janeyg-msft]

Hi, @Mohanad-Mustafa 

I will wait for your reply if you still need help, of course, it is best to provide some sample data.

Best Regards,

Community Support Team _ Janey

[Mohanad-Mustafa]

Hi Amit, thanks for your kind reply.

I do have a calendar table but I don't have business day as a column in it, I have Weekday and Weekday Number columns but no Business day column. I also only have invoice date column in the sales table which is linked to the date column on my calendar. Any clue what Dax formula to use to be able to create a business day column please?

[amitchandak]

@Mohanad-Mustafa , Assuming you have calendar joined with sales date having business days  flag

Business days Measure= calculate(distinctcount(Table[Sale Date]), filter(Date, Date[Business Day]=1))