Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I am creating a Calculated Date table using Min and Max dates as the date range. Frequently I do this using dates from an imported table, but this time I manually entered the MinDate I want. No matter what I do the actual date range does not go past 1/1/2019.
Here's my code:
Date = var MinDate = DATE(2014,1,1) var MaxDate = EOMONTH(TODAY(),-1) RETURN ADDCOLUMNS( FILTER( CALENDARAUTO(), [Date]>= MinDate && [Date]<= MaxDate ), "Year", YEAR([Date]), "Quarter Number", INT(FORMAT([Date], "q")), "Quater", "Q" & INT(FORMAT([Date], "q")), "Quarter Year", INT(FORMAT([Date],"q")) & "Q" & RIGHT(YEAR([Date]),2), "Month End", EOMONTH([Date],0))
It's clear that I want the variable MinDate to be equal to 1/1/2014, and I use the DATE() function to recognize that value as date.
Here are a few visuals of the Date table:
It only created a date table with 1/1/2019 as the earliest date. What am I missing?
Solved! Go to Solution.
Hi @rliebau
Use CALENDAR instead of CALENDARAUTO:
Date = VAR MinDate = DATE ( 2014, 1, 1 ) VAR MaxDate = EOMONTH ( TODAY (), -1 ) RETURN ADDCOLUMNS ( CALENDAR ( MinDate, MaxDate ), "Year", YEAR ( [Date] ), "Quarter Number", INT ( FORMAT ( [Date], "q" ) ), "Quater", "Q" & INT ( FORMAT ( [Date], "q" ) ), "Quarter Year", INT ( FORMAT ( [Date], "q" ) ) & "Q" & RIGHT ( YEAR ( [Date] ), 2 ), "Month End", EOMONTH ( [Date], 0 ) )
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
I fixed it. I forgot that CALENDARAUTO() relies on a base datetime column in an existing table. I had one table imported already that only had a date range in 2019.
Here's the corrected code for anyone interested:
Date = var MinDate = DATE(2014,1,1) var MaxDate = EOMONTH(TODAY(),-1) RETURN ADDCOLUMNS( CALENDAR(MinDate, MaxDate), "Year", YEAR([Date]), "Quarter Number", INT(FORMAT([Date], "q")), "Quater", "Q" & INT(FORMAT([Date], "q")), "Quarter Year", INT(FORMAT([Date],"q")) & "Q" & RIGHT(YEAR([Date]),2), "Month End", EOMONTH([Date],0))
Hi @rliebau
Use CALENDAR instead of CALENDARAUTO:
Date = VAR MinDate = DATE ( 2014, 1, 1 ) VAR MaxDate = EOMONTH ( TODAY (), -1 ) RETURN ADDCOLUMNS ( CALENDAR ( MinDate, MaxDate ), "Year", YEAR ( [Date] ), "Quarter Number", INT ( FORMAT ( [Date], "q" ) ), "Quater", "Q" & INT ( FORMAT ( [Date], "q" ) ), "Quarter Year", INT ( FORMAT ( [Date], "q" ) ) & "Q" & RIGHT ( YEAR ( [Date] ), 2 ), "Month End", EOMONTH ( [Date], 0 ) )
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
72 | |
72 | |
38 | |
31 | |
27 |
User | Count |
---|---|
92 | |
50 | |
44 | |
40 | |
35 |