DAX to get YoY comparison based on text values

[Anonymous]

HI All,

 

My current database has a Fiscal Year convertion because I need the letter "FY" to appear before the year, e.g. FY2122

 

I am trying to show a card with the comparison of the current fiscal year and the previous fiscal year, but it seems I can't filter the same table twice with different criteria with my approach (playing with letters). I also tried tried creating an automated calendar with fiscal years and months, but it doesn't work either.

AA-Avage-YoY = 
        IF(ISFILTERED(tblFTE[FiscalYear]);
            CALCULATE(AVERAGE(tblFTE[Age]);
            FILTER(tblFTE;tblFTE[FiscalYear]=tblFTE[FiscalYear])) 
            - 
            CALCULATE(AVERAGE(tblFTE[Age]);
            FILTER(tblFTE;tblFTE[FiscalYear]="FY"&MID(MAX(tblFTE[FiscalYear]);3;2)-1&RIGHT(MAX(tblFTE[FiscalYear]);2)-1)))

I'd appreciate any help/hint you can give me,

Many thanks!

[amitchandak]

@Anonymous , first create a separate Date/FY Table. This FY format is sortable Create a rank on that

new column

Year Rank = RANKX(all('Date'),'Date'[FY],,ASC,Dense)

measures
This Year = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])))
Last Year = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])-1))

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s