Forum Discussion

sdhn's avatar
sdhn
Icon for Responsive Resident rankResponsive Resident
4 years ago
Solved

New FY Compasion

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.

Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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-Calendar-1-5-Power/ba-p/1187441

     

    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's avatar
    Anonymous
    Not applicable
    4 years ago

    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