LIMIT 100 values with DAX FUNCTION CALENDAR

[agilizy]

Hello everyone, in one of my Power BI reports, I created a dynamic date table using the CALENDAR function. I noticed that there is a limit of 1000 values. When testing with this function :

Dates = VAR MinDate = DATE(YEAR(today())-3,1,1) VAR MaxDate = DATE(YEAR(today()),1,1) -- Date in DATE function format RETURN CALENDAR(MinDate, MaxDate)

I found that the table created ranges from 01/01/2022 to 27/09/2024, even though today() was 03/01/2025 at the time I tested this function.

Do you have any solutions to work around this problem? Thanks to all.

[danextian]

Your Dates calculated table formula returns just fine. Basing on today's date, there are 1097 rows generated.

[DataNinja777 · Accepted solution]

Hi @agilizy ,

You're encountering what seems to be a limit of 1000 values in the table preview in Power BI, but this is not a limitation of the CALENDAR function itself. The function can handle much larger date ranges, and the issue you're seeing is likely related to the Data Preview pane in Power BI Desktop, which, by default, only displays up to 1000 rows.

To confirm that your date range is being generated correctly, you can create measures to check the minimum and maximum dates in your Dates table. Here's a measure you can use to verify:

Min Date = MIN('Dates'[Date])
Max Date = MAX('Dates'[Date])

Add these measures to a card visual in Power BI, and you should see the actual range covered by your Dates table, even if the preview only shows a subset of rows.

Looking at your current DAX formula, I noticed two potential issues. The first is that your MaxDate variable is set to the first day of the current year. If you're looking for a date table that covers up to today's date or the end of the current year, the MaxDate should be adjusted accordingly. The second consideration is the use of the TODAY() function, which evaluates based on the system date when you open the report. If you ran this in early January, for example, your date range might not cover the full period you expected.

To fix this, you can adjust your formula to ensure the date range covers from three years ago up to the end of the current year. Here's an updated version:

Dates =
VAR MinDate = DATE(YEAR(TODAY()) - 3, 1, 1)
VAR MaxDate = DATE(YEAR(TODAY()), 12, 31)
RETURN CALENDAR(MinDate, MaxDate)

This formula will generate dates from January 1st of three years ago through December 31st of the current year. If you want a fixed date range, you can modify the formula to cover a specific period:

Dates = CALENDAR(DATE(2022, 1, 1), DATE(2025, 12, 31))

If your concern is that you're not seeing the full list of dates in the Data Preview, you can adjust the maximum number of rows displayed in Power BI Desktop. Go to File > Options and Settings > Options, then navigate to Data Load > Data Preview, and increase the maximum number of rows to a higher value.

In summary, the issue you’re experiencing is a display limit in the preview pane, not a functional issue with the CALENDAR function. Verify your date range using measures, adjust your formula to cover the desired range, and modify the Data Preview settings if necessary to see more rows.

Best regards,