Date master

[Niraj_vora0106]

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!

[v-yueyunzh-msft · Accepted solution]

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

[Niraj_vora0106]

This works.

Thank you!

[ppm1]

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

[Niraj_vora0106]

Yes M-Query is also another option.

Thank you!

[Thejeswar]

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

[ouaelaam]

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.

[Niraj_vora0106]

Hi,

this is useful in case of current table.

How to do it in Custom Date table???

[ouaelaam]

You can use the dax code provided by SQLBI to create a custom date table.