Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |