Achieving financial fiscal year format of YYYY-YY in DAX

[Matthew77]

Hi Everyone,

After much research I cannot seem to find a way to format my date for the UK fiscal year in my custom calendar table i.e YYYY-YY (2021-22). I have code that I use for my calendar table which is below. I have tried editing the code but im a newbie and the format comes out wrong. The best I could get it was YY-YY but this doesnt match my other data. Any one know how I could achieve this?  

Calendar = 

--Inputs--
VAR WeekStartsOn = "Mon"
VAR FiscalStartMonth = 4 

--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,
        "CalWeekNo", WEEKNUM ( [Date], 2 ),
        "Weekend/Working", IF ( WEEKDAY ( [Date], 2 ) > 5, "Weekend", "Working" ),
        "Day", FORMAT ( [Date], "ddd" ),
        "CustomDate", FORMAT ( [Date], "d/mm" )
    )

 Regards,

Matthew

[amitchandak]

@Matthew77 , Based on the end date of year

Year = year(startofyear([date], "6/30")) & "-" & year(endofyear([date], "6/30")) 

Change year-end 6/30 to your year-end date 

this will not work correct if you calendar do not have start and end date.

I think I discussed that in my video

https://www.youtube.com/watch?v=vlm5_MN6M4Y

https://www.youtube.com/watch?v=LbstAOFD2Vo

[Matthew77]

Thanks for your response Amitchanda, i tried that and also watched your videos. Unfortunately this gives the year in the format of yyyy-yyyy (2021-2022) but I need it to be in the format of yyyy-yy (2021-22). 

[amitchandak · Accepted solution]

@Matthew77 , sorry one small change

Year = year(startofyear([date], "6/30")) & "-" & format(endofyear([date], "6/30"),"YY")

or

Year = format(startofyear([date], "6/30"),"YYYY") & "-" & format(endofyear([date], "6/30"),"YY")

[Matthew77]

This is perfect. Thank you so much 😁