Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
kenyonca
Frequent Visitor

Number of Weeks in a month

I have broken out my estimated Man Weeks per month for a workforce planning chart. But I need to divide this by the total number of weeks in a month to give me my estimated man power needs as an average for the month.

 

I tried using the below formula in an added column in my Date Table but it was coming back with some as saying 6 weeks in a certain month. For my purposes I'm using if it is a Saturday as my weekly cut off. So for example the lsat day of February 2017 would be on the 25th and the month would only contain 4 weeks.

 

1 ACCEPTED SOLUTION
Phil_Seamark
Microsoft Employee
Microsoft Employee

You could add a column to your date table called 

 

Is saturday = if (weekday ([date],1) =7,1,0)

 

Then sum that for each month


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

5 REPLIES 5
Phil_Seamark
Microsoft Employee
Microsoft Employee

You could add a column to your date table called 

 

Is saturday = if (weekday ([date],1) =7,1,0)

 

Then sum that for each month


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

I'm not sure that will work. Take a look at April, which starts on a Saturday. There possibly needs to be more logic involved, but only the original poster will know if this works.

Thats alright @dkay84_PowerBI based on how my company calculates our month ends this solution will work.

That works out perfectly thanks for the help @Phil_Seamark

dkay84_PowerBI
Microsoft Employee
Microsoft Employee

You will need to create a calendar table that calculates and flags the last day of each month based on your logic.  Then you can create a formula that will calculate the date difference between the last day of month (actual) and the flagged last date of month (your logic).  Subtract that difference from the total days in the month and divide by 4 (and sounds like round up will be needed to).

 

I will get back to you with an exact solution but try thinking about what I recommend and see if you can come up with something.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.