Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Calendar Formatting Fiscal Year

I'm trying to add a column to my calendar chart (column Period) that is just a different format of Fiscal Year and Quarter. My end goal is for the column value to be as such - FY15 Q1

 

Here is what I've attempted so far. I'm assuming because the calculations for FY and FYQTR come after the Add Columns portion, that is why its erroring? Can anyone please help me fix this?

 

 

 

Thanks!

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous 

    You add FY and FYQTR column by second addcolumn function ,so you can't find two columns in first table.

    I update your calendar table as below:

     

    Calendar = 
ADDCOLUMNS (
    ADDCOLUMNS (
        ADDCOLUMNS (
            CALENDAR ( DATE ( 2009, 10, 1 ), DATE ( 2040, 09, 30 ) ),
            "Year", YEAR ( [Date] ),
            "MonthNameShort", FORMAT ( [Date], "MMM" ),
            "MonthNumber", MONTH ( [Date] ),
            "Quarter", QUARTER ( [Date] ),
            "YearQuarter",
                FORMAT ( [Date], "YYYY" ) & "/Q"
                    & FORMAT ( [Date], "Q" )
        ),
        "FY",
            IF ( [MonthNumber] >= 10, [Year] + 1, [Year] ),
        "FYQTR",
            IF ( [Quarter] = 4, 1, [Quarter] + 1 )
    ),
    "Period",
        "FY" & RIGHT ( [FY], 2 ) & "Q" & [FYQTR]
)

     

    Due to [FY] is not date type, so Format([FY],"YY") will return to a wrong result "05"("YYYY" = 1905).

    So, you can try right function. Result is as below.

    You can download the pbix file from this link: Calendar Formatting Fiscal Year

     

    Best Regards,

    Rico Zhou

     

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

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous 

    You add FY and FYQTR column by second addcolumn function ,so you can't find two columns in first table.

    I update your calendar table as below:

     

    Calendar = 
ADDCOLUMNS (
    ADDCOLUMNS (
        ADDCOLUMNS (
            CALENDAR ( DATE ( 2009, 10, 1 ), DATE ( 2040, 09, 30 ) ),
            "Year", YEAR ( [Date] ),
            "MonthNameShort", FORMAT ( [Date], "MMM" ),
            "MonthNumber", MONTH ( [Date] ),
            "Quarter", QUARTER ( [Date] ),
            "YearQuarter",
                FORMAT ( [Date], "YYYY" ) & "/Q"
                    & FORMAT ( [Date], "Q" )
        ),
        "FY",
            IF ( [MonthNumber] >= 10, [Year] + 1, [Year] ),
        "FYQTR",
            IF ( [Quarter] = 4, 1, [Quarter] + 1 )
    ),
    "Period",
        "FY" & RIGHT ( [FY], 2 ) & "Q" & [FYQTR]
)

     

    Due to [FY] is not date type, so Format([FY],"YY") will return to a wrong result "05"("YYYY" = 1905).

    So, you can try right function. Result is as below.

    You can download the pbix file from this link: Calendar Formatting Fiscal Year

     

    Best Regards,

    Rico Zhou

     

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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thanks Anonymous ! This is the solution I ended up coming to on my own.