Custom Date Table - Same Month Returning as 2 Month Numbers

[Anonymous]

Hello everyone!

I created a custom Date Table for our Fiscal Year where February = Month 1 and built my custom columns from this. It seemed to be working fine, however, I tried to recreate custom column, but it defaults 2/1/2020 to Month 1 and 2/2/2020 to Month 2.  I tried to recreate the issue on the table where the month numbers are correctly pulling and the same thing happens. Help!

Base Table: 

Month Number:

Correct Columns:

2/1/2020 recognized as Month 1 and 2/2/2020 as Month 2

[V-pazhen-msft · Accepted solution]

@Anonymous 

Same issue happened to me with month(), you may used the following column instead.

Column = IF(([Date].[MonthNo])=1,12,([Date].[MonthNo])-1)

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[Fowmy]

@Anonymous 

Create your month number column using the following code and try:

 Month Number =  MONTH(EOMONTH([Date],-1))

[Anonymous]

Thank you!

[Anonymous]

You could do a switch or nested if statement 

so if month from date is February then 2 if month from date is March then 3 etc. 

last thing is make a calendar in excel based on what you want and then query it in. 

[Anonymous]

Thanks! My issue is it worked in the past, so not sure why it stopped working.

[Anonymous]

Oh ok I wonder If the format of the date column changed. Have you tried adjusting it in column settings? that might of caused the issue if it worked previously. 

[Anonymous]

Nothing has changed... in fact when I tried to recreate the month number column on the same table with the correct month numbers, I get the same issue. Thanks for replying!

[Anonymous]

Hey @Anonymous 

this one is weird i opened up my laptop duplicated your query and then couldn't get the same issue.  i get the corrected result.   not sure what is happening in you report.  any chance you could send a copy to see what is happening?