Calculated Metric for last two months

[anurag0105]

Hi,

In Metric 4 (M4) It is the sum of M1, M2 and M3 till the 3rd last month of the selected till date which simple but things gets messy for calculation of last two months. To calculate M4 for last two months it should take the average of M5 last 3rd and 4th month and than add M1+M2+M3. I am stuck on average part and how to show different calculations in same metric.

Service Month	3/1/2019	4/1/2019	5/1/2019	6/1/2019	7/1/2019	8/1/2019
M1	100	100	100	100	100	100
M2	75	65	12	10	10	5
M3	10	15	5	20	10	5
M4	M1+M2+M3	M1+M2+M3	M1+M2+M3	M1+M2+M3	AVERAGE(1.20,1.00)+M1+M2+M3	AVERAGE(1.20,1.00)+M1+M2+M3
M5	1.50	6.50	1.20	1.00	2.90	2.90
			AVERAGE

Thanks,

Anurag 

[chrismarlow]

Hi,

I think the answer to this is going to be different dependent on how your data is structured, so;

Are M1, M2, M3 & M5 columns in your data, or,

Are M1, M2, M3 & M5 values for a 'Metric' column on a table that also has a Value column, e.g.

Service Month, Metric, Value

3/1/2019, M1, 100

3/1/2019, M2, 75

etc.

Cheers,

Chris.

[anurag0105]

hi,

M1,M2,M3 etc are columns in my data.

Regards,

Anurag 

[chrismarlow]

Hi,

Then you should be able to do  something like the following.

First create some variables covering the dates of interest (note my date format is DD/MM/YYYY & you may need to do something so the last date gets calculated);

Then you can use an If with TOTAL & some set analysis to only add on the average when you need it (& average the correct two dates);

Let me know if I have misunderstood.

Cheers,

Chris.