Forum Discussion

jayjani's avatar
jayjani
Helper I
6 years ago
Solved

YoY Calulation not working

Hello, I am trying to calculate YoY and QoQ for my data and tried various different techniques but to no avail. I am able to correctly mark Previous Year but whenever I try to calculate Sales data...
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, jayjani 

     

    I'd like to suggest you create a Date table. I created data to reproduce your scenario.

    Table:

    Calendar:

     

    Calendar = CALENDARAUTO()

     

     

    There is a relationship between two tables.

     

    You may a measure as follows.

     

    LY Sales1 = 
IF(
    ISFILTERED('Calendar'[Date].[Year]),
    CALCULATE(
        SUM('Table'[Sales]),
        DATESYTD(SAMEPERIODLASTYEAR('Calendar'[Date]))
    )
)

LY Sales2 = 
var _year = SELECTEDVALUE('Calendar'[Date].[Year])
return
CALCULATE(
    SUM('Table'[Sales]),
    FILTER(
        ALLSELECTED('Table'),
        YEAR('Table'[Date]) = _year-1
    )
)

     

     

    Result:

     

    Best Regards

    Allan

     

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

     

     

     

v-alq-msft's avatar
v-alq-msft
Community Support
6 years ago

Hi, jayjani 

 

I'd like to suggest you create a Date table. I created data to reproduce your scenario.

Table:

Calendar:

 

Calendar = CALENDARAUTO()

 

 

There is a relationship between two tables.

 

You may a measure as follows.

 

LY Sales1 = 
IF(
    ISFILTERED('Calendar'[Date].[Year]),
    CALCULATE(
        SUM('Table'[Sales]),
        DATESYTD(SAMEPERIODLASTYEAR('Calendar'[Date]))
    )
)

LY Sales2 = 
var _year = SELECTEDVALUE('Calendar'[Date].[Year])
return
CALCULATE(
    SUM('Table'[Sales]),
    FILTER(
        ALLSELECTED('Table'),
        YEAR('Table'[Date]) = _year-1
    )
)

 

 

Result:

 

Best Regards

Allan

 

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

 

 

 

jayjani's avatar
jayjani
Helper I
6 years ago

Thank you so much, Allan and Greg. Appreciate all your help. It was an issue with my calculated date column. There was a week level granularity and I was using month field.


I am facing another issue now. When I calculate the YoY Change % even using DATESYTD filter, it still calculates entire YoY Change. If you see the image below, the values for 2020 are too high because its just partial YTD data. I used Allan's formula for calculating Last Year Sales value.