YoY Calulation not working

[jayjani]

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.

[v-alq-msft · Accepted solution]

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]

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.

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

That said, See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...