Forum Discussion

dokat's avatar
dokat
Icon for Post Prodigy rankPost Prodigy
4 years ago

YoY Calculation doesn't work with a slicer

Hi,

 

I have a data table like below where i'd like to select a date in a slicer and calculate Year over Year sales.

Slicer has 3 date options. Last Year, Last Month and Year to date and it is based on "Slicer Date" column in the table.

 

I created measure TY: = Calculate(SUM('P&L'[Values]), ALLSELECTED('P&L'[Slicer Dates]))

                              LY = CALCULATE(SUM('P&L'[Values]),DATEADD('P&L'[Slicer Dates],-1,YEAR))    or tied 
                              LY = CALCULATE(SUM('P&L'[Values]),PREVIOUSYEAR('P&L'[Slicer Dates]))
 YoY sales caculation i use below.
                             Sales Chg= ((CALCULATE(('P&L'[TY]), 'P&L'[P&L] in { "Sales"})/CALCULATE(('P&L'[LY]), 'P&L'[P&L] in { "Sales"})
 
My LY formula doesn't work and returns error calculation YoY. Can anyone help?

 

Calendar YearP&LValuesSlicer Date
12/31/2020Sales1000 
12/31/2021Sales2000Last Year
1/1/2021Sales3000 
1/1/2022Sales4000Last Month
1/31/2021Sales5000 
1/31/2022Sales2500YTD
Need Help

4 Replies

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

    TY: = Calculate(SUM('P&L'[Values]), ALLEXCEPT('P&L'[Slicer Dates]))

    change all to allexcept

    • dokat's avatar
      dokat
      Icon for Post Prodigy rankPost Prodigy
      4 years ago

      mh2587  New formula returned below error message and screenshot

       

      The syntax for '(' is incorrect. (DAX(Calculate(SUM('P&L'[Values]), ALLEXCEPT('P&L'(Slicer Dates])))). 

       

    • dokat's avatar
      dokat
      Icon for Post Prodigy rankPost Prodigy
      4 years ago

      mh2587 when i added that bracket it worked however it removed all filters in the dashboard and returning wrong values.

       

      My original TY formula works the challange is when a slicer selected i can't get Last year [LY} formula to work. 

      The way i am reading is when i select YTD it pulls sales for YTD 2022 but i cant get YTD 2021 and calculate YoY Chg. This issue is with [LY] formula. Please note Slicer Date column i added later on to the table based on values in Calendar Year.

       

      Hope this helps clarify.

       

      Thanks