Pivot - Adding calculated fields between value-fields

Copper Contributor

Trying to create calculated fields between the value-fields of a Pivot-table. Eg I have sales data of the past 10 years for all customers and products. Now filter the last 3 years (eg 2019, 2020 and 2021) and would like to have the Pivot-table to show, besides he actual sales data, also the difference between years (both in absolute values and in percentages). And if I change my filter of years then I'd like the pivot-table to still make the comparisons with Year0 and Year-1 et.c.

Cannot figure this out...

3 Replies

@HL_Office 

That's with data model and DAX measure. They could be relatively simple, everything depends on how your data is structured. Bunch of possible patterns is here Standard time-related calculations – DAX Patterns

Thanks for your response but this isn't what I'm looking for (my bad, sorry).

Made an example of a pivot-table which I've got and added the 'current year vs. previous year' calculations to the right of the pivot (yellow circle). 

My question is: Can I somehow make calculated items / fields in pivot table which show me these differences? And would these calculated fields be smart enough that if I choose to filter on eg years 2019 and 2020 in pivot then the only comparison I'd see is Delta 2020 vs. 2019? 

@HL_Office 

Depends on how your source data is organized. As variant that could be

image.png

which gives result as on your screenshot.