Forum Discussion

unnijoy's avatar
unnijoy
Post Prodigy
5 years ago
Solved

Calender table issue

I am using the below formula to create a calender table.

Calender tabel =ADDCOLUMNS(
CALENDAR(MIN(Date[Month_YR]),MAX(Date[Month_YR])),
"Year",YEAR([Date]),
"Monthn",MONTH([Date]),
"Month",FORMAT([Date],"mmmm"),
"Month Year",FORMAT([Date],"mmm-yy"),
"MonthYearSort",FORMAT([Date],"yyyymm")
)
As i need "Month Year" in date format i change Month Year column from text to Date and change format as mmm-yy. Bust insted of 2020 and gettin all as 2021. 
How to fix it.
Need Help
  • v-henryk-mstf's avatar
    v-henryk-mstf
    5 years ago

    Hi unnijoy ,


    I constructed the same data model, and then I can directly convert the "Month Year" field in the table from Text to Data type.

    Data Type

     

    For the modified date format, the column can only be displayed as text type, and powerbi currently only supports the date type format included by default.


    If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.


    Best Regards,
    Henry


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

6 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    5 years ago

    Hi unnijoy 

    I can't reproduce the issue.  I set up a table called 'Date' containing a column called Month_YR and populated that with dates from 2020 to 2022.  The resulting Calendar Table has dates from 2020 right through to 2022.

    What data have you got in your 'Date'[Month_YR] column?  If you are only getting dates in 2021 that would suggest that the earliest date you have in that table is in 2021. That's what the CALENDAR fucntion is picking up.

    Please supply your data.

    regards

    Phil

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    5 years ago

    Hi unnijoy 

    In your initial post your code showed that you were creating the Calendar Table using the MIN and MAX dates from a table called Date

    CALENDAR(MIN(Date[Month_YR]),MAX(Date[Month_YR]))

    But your latest post shows that you are actually using the MIN and MAX dates from the table[column] called Errors[Month_YR] - what data is in that column?  That's what is detemining the dates/years you get in your Calendar.

    Regards

    Phil

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    5 years ago

    Hi unnijoy ,


    I constructed the same data model, and then I can directly convert the "Month Year" field in the table from Text to Data type.

    Data Type

     

    For the modified date format, the column can only be displayed as text type, and powerbi currently only supports the date type format included by default.


    If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.


    Best Regards,
    Henry


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