Number of Weeks in a month

[kenyonca]

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.

[Phil_Seamark · Accepted solution]

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

[dkay84_PowerBI]

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.

[kenyonca]

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

[kenyonca]

That works out perfectly thanks for the help @Phil_Seamark

[dkay84_PowerBI]

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.