DAX

[Anonymous]

HI,

I want to compare the sales amount of several shops with previous year. All my current year and previous year values are in same table. How to filter the value should calculate only if both the year has same shops? I'm using the below dax, If I use this power BI should not detect the new shops.

Net_sales_Performance = SUMX(CALCULATETABLE('Table1',except(VALUES('Table1'[storeID]),VALUES('Table2'[SHOP NO]))),'Table1'[Performance])

Here, I'm not supposed to use the Table 2. Help me with the solution asap.

Note: Simply current year and previous year has same shop Id so that it can compare with corresponding shops.

TIA

[MattAllington · Accepted solution]

I wrote an article about this a few years ago. https://powerpivotpro.com/2015/08/nested-sumx-or-dax-query/

[Anonymous]

Thank you so much Matt!! Its really worked out !!