Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
kthorp
New Member

URGENT: Calendar created with CALENDARAUTO function has stopped working

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" )
    )
 
 
3 REPLIES 3
kthorp
New Member

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

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.

daXtreme
Solution Sage
Solution Sage

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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.