Create current FY and Last FY tables from existing single table.

[Anonymous]

Hello Fella's

          I have one single table which contains Sales data for two FY. Like below  

      I want to create separate tables on the basis of FISCALYEAR to compare the sale between two FY.

[amitchandak]

@Anonymous , One way is you deal FY with Date. You can use datesytd with you choice of end date

Assume end date 3/31

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"3/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"3/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"3/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"3/31"))

The second option is only at the year level. Create a separate table with distinct FY (Assume table name as date) and then create a rank

Date  = distinct(Table[FISCALYEAR]) //New Table

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

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

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

[Anonymous]

@amitchandak 

Thanks a ton for the quick reply, but I forgot to mention that I want to compare the Item Wise - FY wise sale.

e.g. ITEMA - FY -2018-2019 sale was 21000, and FY-2019-2020 was 20500.

Hope you don't mind helping. 🙂

[amitchandak]

@Anonymous , As long as use Datesytd with date table or separate year table. You should be able to compare items because all on those tables will not cause all for items.  and you should able to compare the same item. Try these options and let me know if you face the issue.

My file is attached

[Anonymous]

Hi @amitchandak ,

 I managed with Second Option.

Thank you so much for the instant aid. 🙂