Calender table issue

[unnijoy]

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.

[v-henryk-mstf · Accepted solution]

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.

[PhilipTreacy]

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

[PhilipTreacy]

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

[unnijoy]

hi @PhilipTreacy ,

Below is the data set.

[unnijoy]

hi @PhilipTreacy ,

in the calender table month Year was in Text format. I change it to date. And in the format opetion i manually enter "mmm-yy"

[unnijoy]

this is the format.

01-11-2020