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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors