Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Custom Date Table - Same Month Returning as 2 Month Numbers

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

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        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. 

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    5 years ago

    Anonymous 

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

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



     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you!

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.