New FY Compasion

[sdhn]

Hello All,

I have a table contains values from July 2018 to June 2021.

This data about Tickets creation.  "Ticket Number" is a primary column. I do have "Creation Date" column.

I need to calculate changes of total counts of "Tickets Number" in the Fiscal Year.   Formual to calculate changes is "Previous FY - Current FY  / Current FY". 

I need to grep values sapartelty for following periods.

FY18-19  = values for Jul 2018 to Jun 2019. 

FY19-20 = values for Jul 2019 to Jun 2020.

FY20-21 = values for Jul 2020 to Jun 2021.

Requires two help

1.  Total counts for each Fiscal Year Period.

2. Measure or Calculated Column to calculate changes.

Any help will be appreciated.

[Anonymous · Accepted solution]

Hi  @sdhn ,

I created some data:

Here are the steps you can follow：

1. Create calculated column.

FY =
SWITCH(
    TRUE(),
    'Table'[date]>=DATE(2018,7,1)&&'Table'[date]<DATE(2019,7,1),"FY2018 - FY2019",
    'Table'[date]>=DATE(2019,7,1)&&'Table'[date]<DATE(2020,7,1),"FY2019 - FY2020",
    'Table'[date]>=DATE(2020,7,1)&&'Table'[date]<DATE(2021,7,1),"FY2020 - FY2021"
)

2. Create measure.

FY2018 - FY2019 =
COUNTX(FILTER(ALL('Table'),'Table'[FY]="FY2018 - FY2019"),[Ticket Number])

FY2019 - FY2020 =
COUNTX(FILTER(ALL('Table'),'Table'[FY]="FY2019 - FY2020"),[Ticket Number])

FY2020 - FY2021 =
COUNTX(FILTER(ALL('Table'),'Table'[FY]="FY2020 - FY2021"),[Ticket Number])

Change1 =
DIVIDE([FY2019 - FY2020]-[FY2018 - FY2019],[FY2018 - FY2019])

Change2 =
DIVIDE([FY2020 - FY2021]-[FY2019 - FY2020],[FY2019 - FY2020])

3. Result:

Best Regards,

Liu Yang

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

[amitchandak]

@sdhn , with date table you can use time intellignece

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"6/30"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"6/30"))

If you already have FY define in calendar

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

Creating Financial Calendar - From Any Month
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calend...

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