Forum Discussion
Date master
Hi community,
I've a doubt regarding Date Master,
I tried creating Custom Date table.
I've data from 2018 but my Date table is considering weird dates as you can see in the snapshot.
How do I get rid of those dates????
Please help!
Hi , Niraj_vora0106
According to your description, you want to create a date table. And now you use the calendarauto() function.
For this , i do not recommend you to use the calendarauto() function. Because it auto-create a date table from all the date type column from your date source, it will affect performance.
I recommand you to use calendar() function to create a date table , like this:
Calendar = ADDCOLUMNS( CALENDAR(FIRSTDATE('Table'[Date]),LASTDATE('Table'[Date])), "Year", YEAR ( [Date] ), "Quarter", ROUNDUP(MONTH([Date])/3,0), "Month", MONTH([Date]), "Week", weeknum([Date]), "Year_Quarter", year([date]) & "Q" & ROUNDUP(MONTH([Date])/3,0), "Year_Month", year([Date]) * 100 + MONTH([Date]), "Yeer_WeekNum", year([Date]) * 100 + weeknum([Date]), "Weekday", WEEKDAY([Date],2) )Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
8 Replies
Hi,
You can define a Min Year and a Max Year to your date table.
Please refer to the very good instructions from SQLBI :
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/✔️
If this post is helpful, please consider marking it as a solution so that other users may find it more easily.
Hi,
this is useful in case of current table.
How to do it in Custom Date table???
You can use the dax code provided by SQLBI to create a custom date table.
Hi Niraj_vora0106,
The CALENDARAUTO() function basically displays all dates starting from the year start of the minimum date in the model and ending with the fiscal year end date of the maximum date in the column.
To solve this, try to find out if any table in the data model is having dates with wierd year
You can create custom Date tables with M (query editor) or DAX, and there are many good examples out there to start with. Here is one.
No Sort Date Tables! – Hoosier BI
Pat
Yes M-Query is also another option.
Thank you!
Hi , Niraj_vora0106
According to your description, you want to create a date table. And now you use the calendarauto() function.
For this , i do not recommend you to use the calendarauto() function. Because it auto-create a date table from all the date type column from your date source, it will affect performance.
I recommand you to use calendar() function to create a date table , like this:
Calendar = ADDCOLUMNS( CALENDAR(FIRSTDATE('Table'[Date]),LASTDATE('Table'[Date])), "Year", YEAR ( [Date] ), "Quarter", ROUNDUP(MONTH([Date])/3,0), "Month", MONTH([Date]), "Week", weeknum([Date]), "Year_Quarter", year([date]) & "Q" & ROUNDUP(MONTH([Date])/3,0), "Year_Month", year([Date]) * 100 + MONTH([Date]), "Yeer_WeekNum", year([Date]) * 100 + weeknum([Date]), "Weekday", WEEKDAY([Date],2) )Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
This works.
Thank you!
