cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mohanad-Mustafa
Advocate III
Advocate III

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

 

Mohanad

 

 

6 REPLIES 6
v-janeyg-msft
Community Support
Community Support

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

 

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

@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

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
Advocate III
Advocate III

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
Super User
Super User

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
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 !!

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

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

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!