Calendar Table returning error: The expression specified in the query is not a valid expression

[Previsible]

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.

[johnt75 · Accepted 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.

[Previsible]

Thank you! It's a simple answer but this worked without an issue right out of the gate.

[Poojara_D12]

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=04iw9SYI2HN80HKS 

[Previsible]

I 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." 

[lbendlin]

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.

[Previsible]

I spoke too soon, it returned the error "The start date in Calendar function can not be later than the end date." again 😪

[lbendlin]

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.

[Previsible]

I am doing that and the moment when I add anything to the list with a date later than September 28, 2023, that error seems to appear (The start date in Calendar function can not be later than the end date.) even if I enter it manually step by step in the list. (Avoiding copy+paste)

Attached is an export of the data I am trying to put in the list
Google Drive Link to DS_Asset QA csv 

The expected outcome is a Calendar table with a list of dates.

I want to use this table to create a splice including the Previous Quarter, Last Year, Previous Month, etx

[lbendlin]

Can't seem to reproduce the issue?

[Previsible]

It's possible that it's a limitation in sharepoint as opposed to the plain export through excel, I obviously can't share the sharepoint data directly but that's  the export so I'm not sure what else to say, I'm trying to build a new list as we speak