Forum Discussion
how to create a date table with DAX code where there is no end date
I am trying to create a date table in Power BI to create a relationship between three queries. I wanted to know how do I create the date table with no ending date. I have a start date (which is 2000-01-01) but I want to be able to refresh my report and get data for future dates as well. Is there a formula I can use to create that date table? Your help is much appreciated!
Hey Anonymous
this article describe how to create a table using DAX:
https://docs.microsoft.com/en-us/power-bi/desktop-calculated-tables
This is the code to create a table with a given start date that ends with a date that is 10 days ahead of today, there are no quotation marks around the end date parameter:
Dates = CALENDAR("2000-01-01", today( ) + 10)Regards,
Tom
8 Replies
- TomMartens
Super User
Hey Anonymous ,
this DAX snippet creates Calendar table with a given start Date and a dynamic end date, where the end date is determined by the max invoice date from the table Fact Sale:
Calendar = var DateStart = "2000-01-01" --var DateEnd = today() + 10 var DateEnd = MAX('Fact Sale'[Invoice Date Key]) return CALENDAR(DateStart , DateEnd)You may also check the DAX function CALENDARAUTO(...): https://dax.guide/calendarauto/
Hopefully, this provides the idea you are looking for.
Regards,
Tom
- TomMartens
Hey Anonymous
this article describe how to create a table using DAX:
https://docs.microsoft.com/en-us/power-bi/desktop-calculated-tables
This is the code to create a table with a given start date that ends with a date that is 10 days ahead of today, there are no quotation marks around the end date parameter:
Dates = CALENDAR("2000-01-01", today( ) + 10)Regards,
Tom
- VasTg
Memorable Member
Anonymous
Refer to this article to create a date dimension based on the fact. So when you add data everyday, the date will also be added dynamically.
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
If it helps, mark it as a solution
Kudos are nice too
