cancel
Showing results for
Did you mean:

## Calculate Fiscal Business Days for 2021 (Day Counter)

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

6 REPLIES 6
Community Support

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
``````

Best Regards,

Community Support Team _ Janey

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

Community Support

If problem still persists, pls let me know.

Best Regards,

Community Support Team _ Janey

Community Support

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

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?

Super User

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

Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!

Announcements

#### The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

#### Check it Out!

Join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!