Table : how to includes gap with year N-1

[Arkhos94]

I have a database looking roughly like this :

Account number	Category	Spending in €	Year

Year can be : 2022, 2021, budget 2022

I have a measure summing the spending column for each category (category being things like gross profit, turnover...) => to make it simple, measure of category 1 is sum for category = category 1, measure of category 2 is sum for category = category 1 or 2, measure of category 3 is sum for category = category 1 or 2 or 3 (and so on)

I show this measure it in my report in a matrix like this :

	2021	2022	Budget 2022
Category 1
Category 2
Category 3

(year is in column, the measure for each category is in line, I checked the option "Show On rows" in the value/option)

Up to that => no issue. Problem : I have been asked to modify the report like this : 

	2021	2022	Budget 2022	Gap 2022 vs 2021	Gap 2022 vs budget 2022
Category 1
Category 2
Category 3

I have not a single clue on how to do it. Create a measure to caculate the gap 2022/2021 and 2022/budget 2022 is easy with calculate

Problem is :

I don't have a clue on how to show these mesure as shown above

as each category have its own measure to calculate the sum of spending, calculate the gape with a measure will force me to create 2 new measure for each category AND update each of them each year

Any idea on how to do it ?

[v-henryk-mstf]

Hi @Arkhos94 ,

What is the exact formula you created? Can you provide the relevant test data and describe it so that I can answer it for you as soon as possible.

Looking forward to your reply.

Best Regards,
Henry

[Arkhos94]

Hello

The exact data look like this (with rougly 92k lines in the real base) :

Spending €	Year	Account number
100	2022	201220
300	2021	301210
120	2021	201220
200	2022 budget	301210
150	2022 budget	201220
350	2022	301210

This table is connected to a second table (with over 3000 accounts number in the real base) :

Account number	Category
201220	2 - Gross profit
301210	1 - Turnover

Measures are like this

Turnover = calculate(sum([Spending €]),[category]="1-Turnover")

Gross profit= calculate(sum([Spending €]),[category]="2-Gross Profit")+[Turnover]

I stopped with 2 category but there are 3 more : margin, contribution and then net result. Margin sum category 3 then add Gross profit, Contribution sum category 4 then add margin, net result sum category 5 then add contribution

There are then many more measures (maybe 2 dozen, for financial analysis) like calculating Gross Profit in % by dividing Gross profit with Turnover. 

I cannot give the real data as it is the financial results and mapping of my company 

But the tables and measure are the correct one