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

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
            )
        )
    )
)