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

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 II
Super User II

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

View solution in original post

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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors