Calculate previous year based on this year condition

Question

Let's say i have a table called Orders with number of units sold by resellers in Country by date

Country ResellerID Date   Units
3       1          May-17 7
3       1          May-16 4
3       2          May-16 3
2       3          May-16 5
2       3          May-17 8

I have to compare total units sold in specific month basing on a slicer selection to the last year total units in the corresponding month. This is relatively easy and I do this using the formula below

UnitsLastYear = CALCULATE(SUM(Orders[Units]),SAMEPERIODLASTYEAR(Orders[Date]))

So for May-17 chosen on the slicer I have:
This year = 15
Last Year = 12

However there is another condition for calculating last year's value and this is that I should only take Resellers that sold anything in current year's selected month so in this example ResellerID = 2 should be excluded from last year calculation and Last Year should have value of 9

Does anyone can help how to transform this calculation so that it would rflect this condition?

Alexis Olson Alexis Olson · Accepted Answer · 2018-01-17T17:12:53

Try modifying it to the following:

= CALCULATE(SUM(Orders[Units]),
            SAMEPERIODLASTYEAR(Orders[Date]),
            Orders[ResellerID] IN VALUES(Orders[ResellerID]))

This will only sum the [ResellerID] values that appear in the current filter context. Since there are no ResellerID = 2 in May-17, those are excluded from the previous year calculation.

Calculate previous year based on this year condition

1 Answers