Grand Total Sum of Last Year

[RookiePowerBI19]

Hi!

Need some basic assistance for an easy DAX command. Assume I have a table with 2 columns

'Sales'[Period] - Year and Month combined (202001, 202002 etc).

'Sales'[KG] - Numerical values in KG

Now assume I want to have a pivot table with Periods between 202201 and 202212 where I present the sales in KG for those periods but also last years sales ('Sales'[Period]-100). The difference between this year and last year in that column is 100 for each value. 

I've tried

Calculate(

SUM('Sales'[KG],

FILTER(

ALL['Sales'[Period]),

'Sales'[Period]=MAX('Sales'[Period])-100

)

)

This works great for all the individual Periods but not in the Grand Total. The Grand Total only shows the sum for the last individual value (202212).

Can anyone help out here? 

[daXtreme · Accepted solution]

Hi @RookiePowerBI19 

// Base measure
[Total KG] = SUM( 'Sales'[KG] )

// Derivative
[Total KG LY] = // last year's KG
    SUMX(
        DISTINCT( 'Sales'[Period] ),
        var IteratedPeriod = 'Sales'[Period]
        var KGLastYear = 
            CALCULATE(
                [Total KG],
                'Sales'[Period] = IteratedPeriod - 100
            )
        return
            KGLastYear
    )

Event though the above works, I would kindly warn you that this model should be changed asap as it's not correct. For correct models please consult this article: Understand star schema and the importance for Power BI … (bing.com)