How to get financial year with dax table wit this format (2018/2019) ?

[Anonymous]

Hi

I have created a Date table using the below DAX function, (please note I have taken the below function from one of the community posts)

Date = 

VAR FirstFiscalMonth = 7 -- First month of the fiscal year

VAR FirstDayOfWeek = 1 -- 0 = Sunday, 1 = Monday, ...

VAR FirstSalesDate = MIN ( 'Table[Sales date])

VAR LastSalesDate = MAX ('Table[Sales date])

VAR FirstFiscalYear = -- Customizes the first fiscal year to use 

    YEAR ( FirstSalesDate ) 
    + 1 * ( MONTH ( FirstSalesDate ) >= FirstFiscalMonth && FirstFiscalMonth > 1)
    
VAR LastFiscalYear = -- Customizes the last fiscal year to use 

    YEAR ( LastSalesDate ) 
    + 1 * ( MONTH ( LastSalesDate ) >= FirstFiscalMonth && FirstFiscalMonth > 1)

RETURN
GENERATE ( 
    VAR FirstDay = 
        DATE ( 
            FirstFiscalYear - 1 * (FirstFiscalMonth > 1), 
            FirstFiscalMonth,
             1 ) 
    VAR LastDay = 
        DATE ( 
            LastFiscalYear + 1 * (FirstFiscalMonth = 1), 
            FirstFiscalMonth, 1
         ) - 1 
    RETURN
         CALENDAR ( FirstDay, LastDay ), 
         
    VAR CurrentDate = [Date]
    VAR Yr = YEAR ( CurrentDate ) -- Year Number 
    VAR Mn = MONTH ( CurrentDate ) -- Month Number (1-12) 
    VAR Mdn = DAY ( CurrentDate ) -- Day of Month 
    VAR DateKey = Yr*10000+Mn*100+Mdn 
    VAR Wd =                     -- Weekday Number (0 = Sunday, 1 = Monday, ...) 
        WEEKDAY ( CurrentDate + 7 - FirstDayOfWeek, 1 ) 
    VAR WorkingDay =             -- Working Day (1 = working, 0 = non-working)
     ( WEEKDAY ( CurrentDate, 1 ) IN { 2, 3, 4, 5, 6 } )
    VAR Fyr =           -- Fiscal Year Number 
        Yr + 1 * ( FirstFiscalMonth > 1 && Mn >= FirstFiscalMonth ) 
    VAR Fmn =           -- Fiscal Month Number (1-12) 
        Mn - FirstFiscalMonth + 1 + 12 * (Mn < FirstFiscalMonth) 
    VAR Fqrn =          -- Fiscal Quarter (string) 
        ROUNDUP ( Fmn / 3, 0 ) 
    VAR Fmqn =
         MOD ( FMn - 1, 3 ) + 1 
    VAR Fqr =           -- Fiscal Quarter (string) 
        FORMAT ( Fqrn, "\Q0" ) 
    VAR FirstDayOfYear = 
        DATE ( Fyr - 1 * (FirstFiscalMonth > 1), FirstFiscalMonth, 1 ) 
    VAR Fydn = 
        SUMX ( 
            CALENDAR ( FirstDayOfYear, CurrentDate ), 
            1 * ( MONTH ( [Date] ) <> 2 || DAY ( [Date] ) <> 29 ) 
        ) 
        RETURN ROW (
             "DateKey", INT ( DateKey ), 
             "Sequential Day Number", INT ( [Date] ), 
             "Year", FORMAT( CurrentDate, "yyyy"), 
             "Year Month", FORMAT ( CurrentDate, "mmm yyyy" ), 
             "Year Month Number", Yr * 12 + Mn - 1, 
             "Financial Year",  Yr & "/" & Fyr, 
             "Financial Year Number", Fyr, 
             "Financial Year Quarter", Fqr & " " & Yr & "/" & Fyr,
             "Financial Year Quarter Number", CONVERT ( Fyr * 4 + FQrn - 1, INTEGER ), 
             "Financial Quarter", "F" & Fqr, 
             "Month", FORMAT ( CurrentDate, "mmm" ), 
             "Financial Month Number", Fmn, 
             "Financial Month in Quarter Number", Fmqn, 
             "Day of Week", FORMAT ( CurrentDate, "ddd" ),
             "Day of Week Number", Wd, 
             "Day of Month Number", Mdn, 
             "Day of Fiscal Year Number", Fydn, 
             "Working Day", IF ( WorkingDay, "Working Day", "Non-Working Day" ) 
             )
    )

The table is working fine, However, I need the financial year in the format of 2020/2021, 2019/2020. My financial years start from July to June. I am using below financial year as follow

"Financial Year", Yr & "/" & Fyr,

Some of the financial year columns come in 2019/2019 0r 2021/2021. Please see the below screenshot

Attached pbix file https://we.tl/t-jyiqqZVabB

 Something is wrong. I am not able to figure it out. Is anyone can assist me?

[amitchandak · Accepted solution]

@Anonymous ,

You can get

Start year= startofyear([date], "6/30" )

end year= endofyear([date], "6/30")

FY Year = year([Start year]) &"/" & year([End year])

refer if needed

Creating Financial Calendar - From Any Month
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calendar-1-5-Power/ba-p/1187441