How to add last year and current year

tz9lawa

Hi

I have monthly data by segment by product family. - Refer to document attached

 

I would like to build dashboard to display last year, current year and variance column.

 

Therefore, i build 1 module, source, last year, current year and variance line item

Question:-

How to input formula to grab data for FY20?

How to input formula to grab data for FY21?

 

Beside year, i also need half year data

		FY21	FY20	Variance
		1H	2H
		AUDm	AUDm	AUDm	%
A	PA
	PB
	PC
B	PA
	PB
	PC
C	PA
	PB
	PC

tz9lawa

thank you for your advice.

 

Without formula added to last year and current year, how to calculate variances?

 

thanks

tz9lawa

as per advice, i manage to build module to cater current year and prior year data.

Using subset.

with this blueprint setting, are we able to use formula to calculate column Volume AUDm and CM/t AUDm?  

 

	FY21 vs. FY20
		Contribution Margin					Volume						CM/t
		FY21	FY20	Variance		FY21	FY20	Variance		FY21	FY20	Variance
Segment	Product Family	AUDm	AUDm	AUDm	%		kt	kt	kt	%	AUDm		AUD/t	AUD/t	AUD/t	%	AUDm
A	PA	20	12	+8.0	+67%		81	80	1	+1%	+0.14		246	149	+97	+65%	+8.4
	PB	15	35	-20.0	-57%		20	18	2	+12%	+4.12		767	2,000	-1,233	-62%	-24.1
	PC	16	12	+4.0	+33%		39	41	-2	-4%	-0.45		410	296	+114	+39%	+4.5
B	PA	81	52	+29.0	+56%		23	22	0	+2%	+0.95		3,575	2,337	+1,238	+53%	+28.0
	PB	23	35	-12.0	-34%		81	80	1	+1%	+0.41		283	436	-153	-35%	-12.4
	PC	65	14	+51.0	+364%		20	18	2	+12%	+1.65		3,322	800	+2,523	+315%	+49.4
C	PA	85	32	+53.0	+166%		39	41	-2	-4%	-1.21		2,180	790	+1,390	+176%	+54.2
	PB	13	34	-21.0	-62%		23	22	0	+2%	+0.62		574	1,528	-954	-62%	-21.6
	PC	65	52	+13.0	+25%		81	80	1	+1%	+0.61		800	648	+153	+24%	+12.4
											Variance Kt * FY20 AUD/T						Volume FY21kt * Variance AUD/t