Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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!
Solved! Go to Solution.
Hey @OPS-MLTSD
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
Hey @OPS-MLTSD ,
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
I tried the DAX code:
Dates - CALENDAR("2000-01-01","today( ) + 10")
It did not work!
For reference, I am looking at accident dates and I just want to know wha is the dax code I should use with a specified start dat and an unspecidied end date
I tried this and its working:
Hey @OPS-MLTSD
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
Awesome you're the best it works!
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
I have already seen this article previsouly and it does not provide me the solution. If anyone could provide me with the actual DAX code, that would be great. Thank you
I always use this below logic in my reports. so there will be no maintenance.
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
User | Count |
---|---|
93 | |
92 | |
84 | |
82 | |
49 |
User | Count |
---|---|
145 | |
142 | |
111 | |
71 | |
55 |