Forum Discussion

lennyt99's avatar
lennyt99
Helper I
3 years ago

Help please asap!

My fiscal Year data is wrong for FY 20 & FY 21... why is this happening but it's correct for FY 22,23,& 24??

This is my Volume LY Formula

 

Volume LY =
CALCULATE(
SUM('Opportunity Tracker 2.0'[Annual Volume LBS]),
DATEADD('Fiscal Year Conversion'[Calendar Date], -1, YEAR))
 
I have a fiscal year calendar already marked as my date table and its correct, because the fiscal year values shown are correct, I just need it to return the correct volume in fiscal year please help with a solution . Maybe a filter or something?

 

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    lennyt99 , seem like related to leap year, Try like

    Volume LY =
    CALCULATE(
    SUM('Opportunity Tracker 2.0'[Annual Volume LBS]),
    SAMEPERIODLASTYEAR('Fiscal Year Conversion'[Calendar Date]))

    • lennyt99's avatar
      lennyt99
      Helper I
      3 years ago

      I've tried that also, however yields the same result. What if a filter is used only for the fiscal year?

      • amitchandak's avatar
        amitchandak
        Super User
        3 years ago

        lennyt99 ,Only for year level

        Rank on FY start Date or Fy year number (sortable)

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

         

        Measure
        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))

    • lennyt99's avatar
      lennyt99
      Helper I
      3 years ago

      Unfortunatly still the same for the period of FY2020. My  fiscal calendar start and end date vary.. for exaple one year it starts 9/30, and another it starts 10/2... so perhaps -1 year that's why it's off? How do I do it by fiscal year?