How to get number of weeks in a month

[kollasv]

Hi all, 

i have a table and a date and a month columns are there in that table

How can i get number of weeks in that partucular month?

Example:

2022 Jan    5 weeks

2022 Feb   4 weeks

2022 Mar  4 weeks

like this i need to create a calculated column automatically,,is there any way to create like this in power bi, please tell me, please help me please....

[speedramps]

Create calandar table:-

Calendar =

CALENDAR(DATE(2021,01,01), DATE(2023,12,31))

Add columns to the table:-

MONTH = MONTH('Calendar'[Date])

YEAR = YEAR('Calendar'[Date])

Week commencing on Sunday =  'Calendar'[Date] - WEEKDAY('Calendar'[Date],1) + 1

Alter the formular if you want the week to start on on another day

WEEKDAY('Calendar'[Date],1) = Sunday,  2= Monday. 3 = Tuesday etc etc

Create  dax measure

Week count =

VAR mysubset= FILTER('Calendar',YEAR('Calendar'[Week commencing on Monday]) = 'Calendar'[Year])

RETURN

CALCULATE(

DISTINCTCOUNT('Calendar'[Week commencing on Sunday]),

mysubset)

Create a table visual and drag ..

YEAR

MONTH

Week count

Voila you have your answer !

Please click thumbs up and accept as solution

[CNENFRNL · Accepted solution]

Weeks = var __SoM = EOMONTH([Date],-1)+1 return CEILING((EOMONTH([Date],0)-__SoM+WEEKDAY(__SoM,2)-6)/7,1)

[arock-well]

@CNENFRNL Could you break down the different parts of your calculation for me? It seems really close for what I'm trying to achieve. However, I would like calculate the number of Fridays in a given month.

For example, if my Date column says '1/4/2023' the resulting number should be 4, as January 2023 has 4 Fridays in the month. If my Date column says '3/8/2023' the resulting number should be 5, as March 2023 has 5 Fridays in the month.

What do I need to change here to make it work for the number of Fridays in a month?

Weeks = var __SoM = EOMONTH([Date],-1)+1 return CEILING((EOMONTH([Date],0)-__SoM+WEEKDAY(__SoM,2)-6)/7,1)

[arock-well]

Actually, I think I figured it out. The WEEKDAY "Return Type" was set to 2, which has Sunday as the last day of the week. I changed it to "16" which has Friday as the end of the week and that appears to work.

[kollasv]

Thankyou sooo muchhhhh

[speedramps]

Try this ...

Create calandar table:-

Calendar =

CALENDAR(DATE(2021,01,01), DATE(2023,12,31))

Add columns to the table:-

MONTH = MONTH('Calendar'[Date])

YEAR = YEAR('Calendar'[Date])

Week commencing on Monday =  'Calendar'[Date] - WEEKDAY('Calendar'[Date],2) + 1
all the formular if you want the week to start on Sunday


Create  dax measure

Week count =

VAR mysubset= FILTER('Calendar',YEAR('Calendar'[Week commencing on Monday]) = 'Calendar'[Year])

RETURN

CALCULATE(

DISTINCTCOUNT('Calendar'[Week commencing on Monday]),

mysubset)

Create a table visual and drag ..

YEAR

MONTH

Week count

Thanks for reaching out for help.

I put in a lot of effort to help you, now please quickly help me by giving kudos.

Remeber we are unpaid volunteers and here to coach you with Power BI and DAX skills and techniques, not do the users job for them. So please click the thumbs up and accept as solution button. 

If you give someone a fish then you only give them one meal, but if you teach them how to fish then they can feed themselves and teach others for a lifetime.  I prefer to teach members on this forum techniques rather give full solutions and do their job. You can then adapt the technique for your solution, learn some DAX skills for next time and soon become a Power BI Super User like me. 

One question per ticket please. If you need to extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

[PrizzC15]

Hi speedramps

May I know how to get numbers of Mondays, Tuesdays, Wednesdays, Thursdays, Fridays, Satursdays, and Sundays of each month / select date (need auto count). TIA

[kollasv]

Hi  Thankyou so much for the valuable time and solution.,,,i want to create a column of number of weeks in the existed table only and i want to calculate number of full weeks in a month which were start with sunday and end with saturday

If you see 2022 Jan 1 st comes on saturday that is comes under 2021 december week, so if i want to calculate 2022 Jan weeks, it starts with sunday i.e.2 nd jan, it means 2 to 8th is one week,9th-15th is second week, 16-22 third week, 23rd to 29th is fourth week ,30 to feb 5th is fifth week (In Jan 5 weeks),,

In Feb first week start from sunday i.e 6th to 12th is first week ,13th to 19th is second week, 20th to 26th third week, 27th to Mar 5th is fourth week(In Feb 4 weeks) like that i want to calculate, please help me 

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly.

Please check the below picture and the attached pbix file.

It is for creating a measure.

Weeknumber count: =
COUNTROWS (
    SUMMARIZE (
        ADDCOLUMNS (
            VALUES ( 'Calendar'[Date] ),
            "@weeknumber", WEEKNUM ( CALCULATE ( MAX ( 'Calendar'[Date] ) ), 21 )
        ),
        [@weeknumber]
    )
)

[kollasv]

Hi  Thankyou so much for the valuable time and solution.,,,i want to create a column of number of weeks in the existed table only and i want to calculate number of full weeks in a month which were start with sunday and end with saturday

If you see 2022 Jan 1 st comes on saturday that is comes under 2021 december week, so if i want to calculate 2022 Jan weeks, it starts with sunday i.e.2 nd jan, it means 2 to 8th is one week,9th-15th is second week, 16-22 third week, 23rd to 29th is fourth week ,30 to feb 5th is fifth week (In Jan 5 weeks),,

In Feb first week start from sunday i.e 6th to 12th is first week ,13th to 19th is second week, 20th to 26th third week, 27th to Mar 5th is fourth week(In Feb 4 weeks) like that i want to calculate, please help me 

[tamerj1]

Hi @kollasv 
Do you want to create it in a date table? I mean what is the granularity of the table that you want to create the column in?

[kollasv]

Hi i want to create a column of number of weeks in the existed table only and i want to calculate number of full weeks in a month which were start with sunday and end with saturday

If you see 2022 Jan 1 st comes on saturday that is comes under 2021 december week, so if i want to calculate 2022 Jan weeks, it starts with sunday i.e.2 nd jan, it means 2 to 8th is one week,9th-15th is second week, 16-22 third week, 23rd to 29th is fourth week ,30 to feb 5th is fifth week (In Jan 5 weeks),,

In Feb first week start from sunday i.e 6th to 12th is first week ,13th to 19th is second week, 20th to 26th third week, 27th to Mar 5th is fourth week(In Feb 4 weeks) like that i want to calculate, please help me professionals....