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