Forum Discussion

nothingman's avatar
nothingman
Regular Visitor
8 years ago
Solved

Calculate last year based on current year filter

Hi,

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 reflect this condition?

  • nothingman's avatar
    nothingman
    8 years ago

    It looks like this one does the trick

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

2 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    8 years ago

    nothingman

     

    Please give this a shot

     

    UnitsLastYear =
CALCULATE (
    SUM ( Orders[Units] ),
    SAMEPERIODLASTYEAR ( Orders[Date] ),
    EXCEPT (
        CALCULATETABLE (
            VALUES ( Orders[ResellerID ] ),
            FILTER (
                Orders,
                YEAR ( Orders[Date] ) = YEAR ( SELECTEDVALUE ( Orders[Date] ) )
            )
        ),
        CALCULATETABLE (
            VALUES ( Orders[ResellerID ] ),
            FILTER (
                Orders,
                YEAR ( Orders[Date] )
                    = YEAR ( SELECTEDVALUE ( Orders[Date] ) ) - 1
            )
        )
    )
)
  • nothingman's avatar
    nothingman
    Regular Visitor
    8 years ago

    It looks like this one does the trick

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