Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
unnijoy
Post Partisan
Post Partisan

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.
1 ACCEPTED SOLUTION
v-henryk-mstf
Community Support
Community Support

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.

啊.PNG

v-henryk-mstf_0-1615444117988.png

Data Type

v-henryk-mstf_1-1615444393958.png

 

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.

View solution in original post

6 REPLIES 6
v-henryk-mstf
Community Support
Community Support

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.

啊.PNG

v-henryk-mstf_0-1615444117988.png

Data Type

v-henryk-mstf_1-1615444393958.png

 

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
Super User
Super User

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


PhilipTreacy
Super User
Super User

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.

datecal.png

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


hi @PhilipTreacy ,

Below is the data set.

Date set.pngcalender table error.png

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"

this is the format.

 

01-11-2020

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.