Forum Discussion
Date Table in Power BI
Hi, I'm very new to Power BI. I have a basic question on date table. Why do we need a date table to use time intelligence functions in Power BI? Why can't we use the existing date in the table (Eg: order date in this example) ?
Thanks,
riazahmedshaik , if it has a continuous date you can. Date should be unique.
Refer I used a date table from the database. Right-click on table and marked that as a date table.
You want to create new
In addition to amitchandak's reply, due to the date in your fact table, many times are not unique or continuous.
So when you need to use a continuous date table to build time intelligence functions in power bi like YTD, you need to build a continuous date table which has unique date in it by dax:
Calendar([Start date],[End date]) OR Calendarauto() Function.
Click link for more info: Calendar() OR Calendarauto()
More info of calendar function: CALENDAR and CALENDARAUTO in Power BI
Calculating MTD, QTD, YTD, Running and Cumulative Total in Power BI
Secret of Time Intelligence Functions in Power BI
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- amitchandak
Super User
riazahmedshaik , if it has a continuous date you can. Date should be unique.
Refer I used a date table from the database. Right-click on table and marked that as a date table.
You want to create new
In addition to amitchandak's reply, due to the date in your fact table, many times are not unique or continuous.
So when you need to use a continuous date table to build time intelligence functions in power bi like YTD, you need to build a continuous date table which has unique date in it by dax:
Calendar([Start date],[End date]) OR Calendarauto() Function.
Click link for more info: Calendar() OR Calendarauto()
More info of calendar function: CALENDAR and CALENDARAUTO in Power BI
Calculating MTD, QTD, YTD, Running and Cumulative Total in Power BI
Secret of Time Intelligence Functions in Power BI
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.
Best Regards,
Rico Zhou