cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
OPS-MLTSD
Helper V
Helper V

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!

 

 

1 ACCEPTED 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 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

8 REPLIES 8
TomMartens
Super User
Super User

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

 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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:

 

Dates = CALENDAR( "2000-01-01",TODAY()+ 10 )

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 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Awesome you're the best it works!

VasTg
Memorable Member
Memorable Member

@OPS-MLTSD 

 

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

Connect on LinkedIn

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.

 

DATES = // TO limit Dates table to only required dates, use dates from Fact table. Ex: Sales
Var MinDateDact = MIN(SalesFact[TxDate])
VAR MaxDateFACT = MAX(SalesFact[TxDate])
RETURN
CALENDAR(MinDateDact,MaxDateFACT)
 

 

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors