cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Hicham
Helper III
Helper III

Open days to count ?

Hello, 

 

I'm working on a dax to calculate the open days in a month. For example, I would like to calculate the open days of March month. So the dax that I wrote is : 

 
OD = CALCULATE(DISTINCTCOUNT('Calendar'[Date]),FILTER('Calendar','Calendar'[Month]=MONTH(NOW())+1))
 
So the result is: 31 days, is the number of all days of March month.
Do you have any tips to calculate the open days of any month? 
 
Thank you in advance.
Regards,
1 ACCEPTED SOLUTION

@Hicham ,

Suppose you have Date Column with Date format so create a new Calculated Column like below:

WeekdayFlag = if(weekday('Calendar'[Date]) = 1,0,if(weekday('Calendar'[Date])=7,0,1))
Then take only Month in table and above calculated column so you'll something like below:
Capture.PNG
Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, and Sales Analysis Report

View solution in original post

5 REPLIES 5
amitchandak
Super User IV
Super User IV

@Hicham , Not very clear what is open days?

 

Total day in current month = day(eomonth(Today(),0)

Total day in next month = day(eomonth(Today(),1)



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@amitchandak Open days are the days that we work on: from Monday to Friday. Example for March the open days are 23, not 31. I would like to calculate it for all months  ! 

 

Regards,

@Hicham 

Work Day = COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(EOMONTH(TODAY(),0)+1, EOMONTH(TODAY(),1)),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@Hicham ,

Suppose you have Date Column with Date format so create a new Calculated Column like below:

WeekdayFlag = if(weekday('Calendar'[Date]) = 1,0,if(weekday('Calendar'[Date])=7,0,1))
Then take only Month in table and above calculated column so you'll something like below:
Capture.PNG
Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, and Sales Analysis Report

View solution in original post

@Hicham , for next month

 

Work Day = COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(EOMONTH(TODAY(),0)+1, EOMONTH(TODAY(),1)),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))


Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Kudoed Authors