Forum Discussion
Calendar Table returning error: The expression specified in the query is not a valid expression
I am racking my brain. I am building a PBi dash from a SharePoint list. Before, the list had only a few lines of test data, so I had no problem creating the calendar table. However, I have a second data source, and I needed to build a calendar based on both sources' tables to make a splicer to filter visuals by date.
At first, I thought the issue was that I had 2 data sources, so I combined them into 1 data source and simplified my DAX
Date =
CALENDAR(
MIN('Deskside QA'[DateofQA]),
MAX('Deskside QA'[DateofQA])
)
But after that, I get the error "The start date in Calendar function can not be later than the end date."
So I added variables to lock down the functions
Date =
VAR MinDate = CALCULATE(MIN('Deskside QA'[DateofQA]))
VAR MaxDate = CALCULATE(MAX('Deskside QA'[DateofQA]))
RETURN
IF(
ISBLANK(MinDate) || ISBLANK(MaxDate) || MinDate > MaxDate,
CALENDAR(TODAY(), TODAY()), // Fallback to a single day calendar if dates are invalid
CALENDAR(MinDate, MaxDate)
)But then I get the error "The expression specified in the query is not a valid expression"
I have been running in circles and throwing in curly brackets but nothing seems to give me a solution.
Try using the CALENDARAUTO() function, that will automatically scan all the date columns in the model and should return a valid table even in the presence of blanks.
10 Replies
Date = CALENDAR(COALESCE(MINX('Deskside QA',[DateofQA]),TODAY()),COALESCE(MAXX('Deskside QA',[DateofQA]),TODAY()))NOTE: Calendars are usually immutable. There is no need to calculate them in either DAX or Power Query. Use a reference table.
I spoke too soon, it returned the error "The start date in Calendar function can not be later than the end date." again 😪
You may want to check the data in the 'Deskside QA' table. You can use the MIN and MAX functions in a different way too, to get the lesser of two dates.
Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.
Hi Previsible
The issue stems from the possibility that your DateofQA column in the Deskside QA table has blank or invalid date values, causing MIN() or MAX() to return BLANK, which is not allowed as input for the CALENDAR function.
Date = VAR MinDate = CALCULATE( MIN('Deskside QA'[DateofQA]), NOT(ISBLANK('Deskside QA'[DateofQA])) ) VAR MaxDate = CALCULATE( MAX('Deskside QA'[DateofQA]), NOT(ISBLANK('Deskside QA'[DateofQA])) ) RETURN IF( ISBLANK(MinDate) || ISBLANK(MaxDate) || MinDate > MaxDate, CALENDAR(TODAY(), TODAY()), // Fallback to avoid errors CALENDAR(MinDate, MaxDate) )By excluding blank values and handling invalid ranges, you ensure CALENDAR gets proper inputs, preventing errors.
The fallback mechanism provides a temporary calendar if the input data is problematic, allowing you to fix the source data later.
Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKSI agree that it's possible the tables have empty entries, maybe a sharepoint list issue (there are no actual empty entries when I look however and overwriting the data doesn't resolve it. )
When I use the DAX in your suggestions I still return an error:
"The expression specified in the query is not a valid table expression."
Try using the CALENDARAUTO() function, that will automatically scan all the date columns in the model and should return a valid table even in the presence of blanks.
Thank you! It's a simple answer but this worked without an issue right out of the gate.
