Pivot - Adding calculated fields between value-fields

%3CLINGO-SUB%20id%3D%22lingo-sub-2493067%22%20slang%3D%22en-US%22%3EPivot%20-%20Adding%20calculated%20fields%20between%20value-fields%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2493067%22%20slang%3D%22en-US%22%3E%3CP%3ETrying%20to%20create%20calculated%20fields%20between%20the%20value-fields%20of%20a%20Pivot-table.%20Eg%20I%20have%20sales%20data%20of%20the%20past%2010%20years%20for%20all%20customers%20and%20products.%20Now%20filter%20the%20last%203%20years%20(eg%202019%2C%202020%20and%202021)%20and%20would%20like%20to%20have%20the%20Pivot-table%20to%20show%2C%20besides%20he%20actual%20sales%20data%2C%20also%20the%20difference%20between%20years%20(both%20in%20absolute%20values%20and%20in%20percentages).%20And%20if%20I%20change%20my%20filter%20of%20years%20then%20I'd%20like%20the%20pivot-table%20to%20still%20make%20the%20comparisons%20with%20Year0%20and%20Year-1%20et.c.%3C%2FP%3E%3CP%3ECannot%20figure%20this%20out...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2493067%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2494559%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20-%20Adding%20calculated%20fields%20between%20value-fields%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2494559%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1090048%22%20target%3D%22_blank%22%3E%40HL_Office%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20with%20data%20model%20and%20DAX%20measure.%20They%20could%20be%20relatively%20simple%2C%20everything%20depends%20on%20how%20your%20data%20is%20structured.%20Bunch%20of%20possible%20patterns%20is%20here%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.daxpatterns.com%2Fstandard-time-related-calculations%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EStandard%20time-related%20calculations%20%E2%80%93%20DAX%20Patterns%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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.