URGENT: Calendar created with CALENDARAUTO function has stopped working

kthorp — Thu, 22 Sep 2022 04:01:11 GMT

Hello.

Hopefully I can find some help here. I have been using the following code for quite some time to create a DAX derived table within my report. I recently updated my version of Power BI to a more recent version and the code has stopped working. I get an error that says:

<ccon> An argument of function 'MONTH' has the wrong data type or the result is too large or too small. If the argument is expected to be a date, tht date must be between January 1, 100 and December 31, 9999. </ccon>

When I open an older copy that I have on my laptop and use the code it works fine - no errors. I am currently working on a remote application server due to the size of the data. However, this does not seem to be an issue because I have tested the error with the same version on my laptop. Yes, I somehow ended up with 2 version on my laptop. I'm guessing it has something to do with autoupdates.

I can't find in my searaches that anyone has been struggling with this issue. Has anyone seen this happen? Any recommendation as to where I should look for an answer. Could it be a setting that needs to be changed?

Most of this code I have scraped from websites and posts and take no credit for it.

URGENT = Needs to get a report to CEO before EOD on 9/22/22. Please help.

CODE:

Calendar =

--Inputs--

VAR WeekStartsOn = "MON"

VAR FiscalStartMonth = 7

--NOTE: Calendar week starts from Monday

--Calculation--

RETURN

ADDCOLUMNS (

CALENDARAUTO ( FiscalStartMonth - 1 ),

"MIndex", MONTH ( [Date] ),

"FiscalMIndex", MONTH ( EDATE ( [Date], - FiscalStartMonth + 1 ) ),

"CalMonth", FORMAT ( [Date], "mmm" ),

"CalQtr", "Q"

& CEILING ( MONTH ( [Date] ), FiscalStartMonth - 1 ) / ( FiscalStartMonth - 1 ),

"CalYear", YEAR ( [Date] ) ,

"Fiscal Week",

VAR FiscalFirstDay =

IF (

MONTH ( [Date] ) < FiscalStartMonth,

DATE ( YEAR ( [Date] ) - 1, FiscalStartMonth, 1 ),

DATE ( YEAR ( [Date] ), FiscalStartMonth, 1 )

)

VAR FilteredTableCount =

COUNTROWS (

FILTER (

SELECTCOLUMNS ( GENERATESERIES ( FiscalFirstDay, [Date] ), "Dates", [Value] ),

FORMAT ( [Dates], "ddd" ) = WeekStartsOn

)

VAR WeekNos =

IF (

FORMAT ( FiscalFirstDay, "ddd" ) <> WeekStartsOn,

FilteredTableCount + 1,

FilteredTableCount

)

RETURN

"Week " & WeekNos,

"Fiscal Qtr", "Q"

& CEILING ( MONTH ( EDATE ( [Date], - FiscalStartMonth + 1 ) ), 3 ) / 3,

"Fiscal Year",

VAR CY =

RIGHT ( YEAR ( [Date] ), 2 )

VAR NY =

RIGHT ( YEAR ( [Date] ) + 1, 2 )

VAR PY =

RIGHT ( YEAR ( [Date] ) - 1, 2 )

VAR FinYear =

IF ( MONTH ( [Date] ) > ( FiscalStartMonth - 1 ), CY & "-" & NY, PY & "-" & CY )

RETURN

FinYear,

"PSU Fiscal Year",

VAR CY1 =

RIGHT ( YEAR ( [Date] ), 4 )

VAR NY1 =

RIGHT ( YEAR ( [Date] ) + 1, 4 )

VAR PY1 =

RIGHT ( YEAR ( [Date] ) - 1, 4 )

VAR PSUFinYear =

IF ( MONTH ( [Date] ) > ( FiscalStartMonth - 1 ), NY1, PY1 )

RETURN

PSUFinYear,

"CalWeekNo", WEEKNUM ( [Date], 2 ),

"Weekend/Working", IF ( WEEKDAY ( [Date], 2 ) > 5, "Weekend", "Working" ),

"Day", FORMAT ( [Date], "ddd" ),

"CustomDate", FORMAT ( [Date], "d/mm" )

)

Re: URGENT: Calendar created with CALENDARAUTO function has stopped working

daXtreme — Thu, 22 Sep 2022 04:37:13 GMT

Since the code has many calls to MONTH, it's not possible to pinpoint the exact line where the problem occurs. You have to find the offending line and then take it from there.

Re: URGENT: Calendar created with CALENDARAUTO function has stopped working

kthorp — Thu, 22 Sep 2022 10:52:26 GMT

Yes, sorry I forgot to mention that. it starts with the very first call to MONTH.

Re: URGENT: Calendar created with CALENDARAUTO function has stopped working

daXtreme — Thu, 22 Sep 2022 14:15:46 GMT

Check if the dates in all the tables you use in the model are within the boundaries accepted by Power BI. The server might have a different range. By the way, is CALENDARAUTO working OK on its own? Try to create a table just by invoking the bare CALENDARAUTO function and then add fields to it successively.

topic URGENT: Calendar created with CALENDARAUTO function has stopped working in DAX Commands and Tips

URGENT: Calendar created with CALENDARAUTO function has stopped working

Re: URGENT: Calendar created with CALENDARAUTO function has stopped working

Re: URGENT: Calendar created with CALENDARAUTO function has stopped working

Re: URGENT: Calendar created with CALENDARAUTO function has stopped working