Forum Discussion

thrillhouse's avatar
thrillhouse
Helper I
4 years ago
Solved

Parameterize Fiscal Year

Hello,

 

I have been attempting to parameterize the fiscal year month start, but have had no luck.  I am building a calendar table that  determines the fiscal year as below.  What I want to do is in 'VAR FiscalStartMonth = 8', add a parameter value in place of 8 so the use can select the fiscal start month.  This does not seem to work as I get the following error "The optional argument of CALENDARAUTO function must be an integer between 1 and 12 inclusive."  

 

So the calendarauto function is not accepting the selected parameter value (1-12) as an integer.  Any thoughts or help on this would be greatly appreciated!  Thanks.

 

Fiscal Calendar =
VAR WeekStartsOn = "Mon"

VAR FiscalStartMonth = 8

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 ), "FY " & NY, "FY " & CY )

RETURN

FinYear,

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

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

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

"CustomDate", FORMAT ( [Date], "d/mm" )
Need Help
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    Hi thrillhouse 

    Thanks for reaching out to us.

    >>I get the following error "The optional argument of CALENDARAUTO function must be an integer between 1 and 12 inclusive."  

    Do not use addcolumns() to create a table.  Addcolumns() will add calculated columns to the given table or table expression. ADDCOLUMNS function (DAX) - DAX | Microsoft Docs

    Just try to use calendarauto() to create a table firstly, then add other columns to it

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    4 years ago

    Hi thrillhouse 

    Thanks for reaching out to us.

    >>I get the following error "The optional argument of CALENDARAUTO function must be an integer between 1 and 12 inclusive."  

    Do not use addcolumns() to create a table.  Addcolumns() will add calculated columns to the given table or table expression. ADDCOLUMNS function (DAX) - DAX | Microsoft Docs

    Just try to use calendarauto() to create a table firstly, then add other columns to it

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.