Incorrect Month

[vjnvinod]

I am seeing some issues with my calendar table

Week5 is technically should be P01, but its shown as P02

this is the formula I am using for month number

MonthNumber =

MONTH ( 'Date'[Date] )

The last week of July should be always July month number, in this case, it comes as 7 & 8, as a result, my Fiscal month number is incorrect

How do I fix the below formula?

MonthNumber =

MONTH ( 'Date'[Date] )

so that the last week of any month should be same number of that month

[v-yalanwu-msft]

Hi,  @vjnvinod ；

Is your problem solved?? If so, Would you mind accept the helpful replies as solutions? Then we could close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

Best Regards,

Community Support Team_ Yalan Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vjnvinod]

@v-yalanwu-msft 

i managed to get this resolved by using

Month number and week start date combination

[v-yalanwu-msft]

Hi, @vjnvinod ;

You could try it.

MonthNumber =
MONTH ( 'Date'[Week End] )

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mahoneypat]

Please see if this calendar table is useful.

445 Calendar with 53-Week Years – Hoosier BI

Pat

[vjnvinod]

@mahoneypat 

544 with the 52-week calendar is what I am trying to build?

Do you have a sample pbix you have handy to use?

[mahoneypat]

Are you comfortable adapting the M code from that article to meet your needs? It shouldn't take much I think.

Pat

[vjnvinod]

@mahoneypat 

I can try, but what happens to those M Code next year? will that work?

[mahoneypat]

Not sure what you mean, but a simpler solution might be to add a column with this expression to your existing Date table query. You can adapt if you week starts on a different day, but this will calculate the fiscal month based on the month of the first day of that week.

= Date.Month(Date.StartOfWeek([Date], Day.Sunday))

Pat