Forum Discussion
Calendar function can not be Blank value.
Hello,
Please can someone help, i have reports that have worked previously and have now stopped.
The error that comes up on the web:
"Couldn't load the data for this visual
The query referenced calculated table 'DateTable' which does not hold any data because evaluation of one of the rows caused an error.
Please try again later or contact support. If you contact support, please provide these details."
When checking the Date Table on Desktop i get:
"The start date or end date in Calendar function can not be Blank value"
- What is the data source for AllSupportTickets table?
If you haven't made any changes to the report, looks like something has changed in the AllSupportTickets table - either the [created] column has been renamed or removed, or the table changed, or there is possibly a relative date filter in the Power Query that has now made that created column empty.
You'll want to figure out what's happening with that table, but as a quick fix you can put an actual date in the formula instead:
CALENDAR (DATE(2020,01,01), eomonth(Today,2))
I personally prefer using M/Power Query to create my data table rather than DAX, but that's just a suggestion. I reserve DAX for dynamic calculations and measures rather than creating data loaded into the model.
https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html
6 Replies
- What is the data source for AllSupportTickets table?
If you haven't made any changes to the report, looks like something has changed in the AllSupportTickets table - either the [created] column has been renamed or removed, or the table changed, or there is possibly a relative date filter in the Power Query that has now made that created column empty.
You'll want to figure out what's happening with that table, but as a quick fix you can put an actual date in the formula instead:
CALENDAR (DATE(2020,01,01), eomonth(Today,2))
I personally prefer using M/Power Query to create my data table rather than DAX, but that's just a suggestion. I reserve DAX for dynamic calculations and measures rather than creating data loaded into the model.
https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.htmlWhat is the data source for AllSupportTickets table? - Jira - Rest API i think.
I have checked the All Support Tickets table and it is empty, only the headings.
I am unsure why this has stopped getting the data.
The reports are dynamic which i think the reports were set up with DAX, they report on the previous 12 months from the current month along with other date ranges 😞
Hi Gem-M ,
Beacuse AllSupportTickets table can't get data, I suggest you could create calendar by calendar(date(2020,1,1),today()), use today() or fixed date, then create relstionship between AllSupportTickets and calendar table, which should work.
You also could check whether AllSupportTickets can't get data is caused by credential problem or network problem.
Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Looks like it was a credential problem for the AllSupportTickets table which in turn caused the other errors.
Thank you for your time and assistance everyone.
- You could also use
Calendar = CALENDARAUTO()
if you really want a quick fix, but as I said in my first reply, you'll want to figure out what's going on under the hood to have broken it, so check the Power Query and see what steps have been created. Gem-M ,
Take out this from min(AllSupportTickets[created]) and check is working or not, what value is it giving ?
CALENDAR (min(AllSupportTickets[created]), eomonth(Today,2)),
or use firstnonblank(AllSupportTickets[created] ,date(2015,01,01)) , that will ensure value you have even it is null.
Also make sure that column is not deleted or rename, if so do the required changes
