Forum Discussion
Report failure at random schedules
Getting this error in few of the scheduled refreshes for a report, while just refreshing the report again fixes the issue.
Resfresh history is giving the below error.
Error fetching data for this visual : MdxScript(Model) (5, 16) Calculation error in measure '<oii>DAX Measures</oii>'[<oii>LY MTD Collection </oii>]: An argument of function 'DATE' has the wrong data type or the result is too large or too small.
Here's the measure value
Hi A454A,
The detailed error suggests this is coming from the DAX measure rather than the refresh schedule itself.
Both _StartOfMonth and _EndDate pass [Business_Date] into YEAR(), MONTH(), DAY() and DATE(). If [Business_Date] evaluates to blank, text, or an invalid date in a particular filter context, DATE() can produce the exact error you are seeing.
I would first test the value and data type returned by [Business_Date] in the failing context. Even if it normally appears static, it is still a measure and can evaluate differently depending on filters or the state of the model immediately after refresh.
I would also simplify the previous-year calculation by shifting the date back twelve months with EDATE, then deriving the start of that month:
LY MTD Collection = VAR BusinessDate = [Business_Date] VAR EndDate = IF ( NOT ISBLANK ( BusinessDate ), EDATE ( BusinessDate, -12 ) ) VAR StartOfMonth = IF ( NOT ISBLANK ( EndDate ), DATE ( YEAR ( EndDate ), MONTH ( EndDate ), 1 ) ) RETURN IF ( ISBLANK ( BusinessDate ), BLANK (), CALCULATE ( DIVIDE ( SUM ( 'FactTable'[Amount] ), 10000000 ), 'FactTable'[TransactionDate] >= StartOfMonth, 'FactTable'[TransactionDate] <= EndDate, 'FactTable'[TransactionType] <> "ExcludedType" ) )Microsoft documents EDATE as the function for returning a date a specified number of months before or after another date. This also handles month-end cases more safely than reconstructing the prior-year date manually.
I would additionally confirm that:
- [Business_Date] returns a true Date/DateTime value rather than formatted text.
- 'FactTable'[TransactionDate] is typed as Date or DateTime.
- [Business_Date] never returns an error or multiple-value result.
- The measure is not using FORMAT() internally, because that would convert the date to text.
- A card visual containing only [Business_Date] still works immediately after the scheduled refresh.
If [Business_Date] is genuinely a fixed business date, I would consider storing it in a one-row configuration table or deriving it from a proper calendar table rather than relying on a measure whose evaluation context may change.
I hope this helps narrow it down. The “capacity or licence” banner appears to be only the visual’s generic failure message; the underlying refresh-history error is specifically identifying the date calculation.
AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.
4 Replies
Thankyou GilbertQ , ShivekMaharaj for Addressing the issue.
Hi A454A ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
Regards,
Chaithanya
Hi @A454A ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
Regards,
Chaithanya
Hi A454A,
The detailed error suggests this is coming from the DAX measure rather than the refresh schedule itself.
Both _StartOfMonth and _EndDate pass [Business_Date] into YEAR(), MONTH(), DAY() and DATE(). If [Business_Date] evaluates to blank, text, or an invalid date in a particular filter context, DATE() can produce the exact error you are seeing.
I would first test the value and data type returned by [Business_Date] in the failing context. Even if it normally appears static, it is still a measure and can evaluate differently depending on filters or the state of the model immediately after refresh.
I would also simplify the previous-year calculation by shifting the date back twelve months with EDATE, then deriving the start of that month:
LY MTD Collection = VAR BusinessDate = [Business_Date] VAR EndDate = IF ( NOT ISBLANK ( BusinessDate ), EDATE ( BusinessDate, -12 ) ) VAR StartOfMonth = IF ( NOT ISBLANK ( EndDate ), DATE ( YEAR ( EndDate ), MONTH ( EndDate ), 1 ) ) RETURN IF ( ISBLANK ( BusinessDate ), BLANK (), CALCULATE ( DIVIDE ( SUM ( 'FactTable'[Amount] ), 10000000 ), 'FactTable'[TransactionDate] >= StartOfMonth, 'FactTable'[TransactionDate] <= EndDate, 'FactTable'[TransactionType] <> "ExcludedType" ) )Microsoft documents EDATE as the function for returning a date a specified number of months before or after another date. This also handles month-end cases more safely than reconstructing the prior-year date manually.
I would additionally confirm that:
- [Business_Date] returns a true Date/DateTime value rather than formatted text.
- 'FactTable'[TransactionDate] is typed as Date or DateTime.
- [Business_Date] never returns an error or multiple-value result.
- The measure is not using FORMAT() internally, because that would convert the date to text.
- A card visual containing only [Business_Date] still works immediately after the scheduled refresh.
If [Business_Date] is genuinely a fixed business date, I would consider storing it in a one-row configuration table or deriving it from a proper calendar table rather than relying on a measure whose evaluation context may change.
I hope this helps narrow it down. The “capacity or licence” banner appears to be only the visual’s generic failure message; the underlying refresh-history error is specifically identifying the date calculation.
AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.
