how to create a date table with DAX code where there is no end date

[OPS-MLTSD]

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!

[TomMartens]

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

[OPS-MLTSD]

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 

[Anonymous]

I tried this and its working:

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

[TomMartens · 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 

[OPS-MLTSD]

Awesome you're the best it works!

[VasTg]

@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

[OPS-MLTSD]

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

[Anonymous]

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)