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 for previous year it returns 0.

I don't have a date column, just FY and month, so I created date column using Date function and also created separate date table and linked to my data table using the date field.

I tried following methods to fetch previous year sales:

1. Used a measure

Sales Last Yr = CALCULATE([Sales], SAMEPERIODLASTYEAR('date'[Date]))

# Didn't work. Returns same year sales
 
2. Created a conditional column Previous Year and used following function
 
LastYearSales =
var previousyr = Sales[PreviousYr]
var prevsales = CALCULATE(SUM(Sales[Sales]),ALL(Sales),Sales[YYYY] = previousyr)
return
IF(ISBLANK(prevsales),0,prevsales)
 
# Returns 0
 
3. Used PBI quick measure.
 
# Returns 0
 
 
Can anyone help? Can it be because my data table has multiple IDs (Buyer ID, Seller ID, etc.)? I even tried Earlier function including most relevant columns in the table but it still returns 0.
 
It always returns dates correctly whether I use my data table or the date table but just doesn't get the Sales data.
 
This is what I am getting. 2nd column is calculated using the Date table and 3rd column without the date table.
 

 

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.

     

     

     

3 Replies

  • 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.