Grand Total Sum of Last Year

RookiePowerBI19 — Tue, 16 Aug 2022 13:47:30 GMT

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?

Re: Grand Total Sum of Last Year

daXtreme — Tue, 16 Aug 2022 13:55:47 GMT

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)

topic Re: Grand Total Sum of Last Year in DAX Commands and Tips

Grand Total Sum of Last Year

Re: Grand Total Sum of Last Year