Calculate Last 3 months Average when two date columns are present

[rookie_963]

Hi
Below is the Table structure I've got, where I have Store_ID, Transaction_date, Upgrade_date (store upgraded), Sales. 

Store_ID	Transaction_Date	Upgrade_date	Sales
11AB	01-01-2021	24-04-2021	100
11AB	12-01-2021	24-04-2021	80
11AB	22-02-2021	24-04-2021	120
11AB	13-03-2021	24-04-2021	200
11AB	14-04-2021	24-04-2021	250
11AB	24-04-2021	24-04-2021	110
11AB	05-05-2021	24-04-2021	290
11AB	06-05-2021	24-04-2021	70
11AB	07-05-2021	24-04-2021	130
11AB	08-06-2021	24-04-2021	50
24AC	01-05-2022	06-09-2022	140
24AC	12-06-2022	06-09-2022	180
24AC	22-06-2022	06-09-2022	160
24AC	15-07-2022	06-09-2022	0
24AC	13-08-2022	06-09-2022	250
24AC	14-08-2022	06-09-2022	250
24AC	24-08-2022	06-09-2022	170
24AC	05-09-2022	06-09-2022	190
24AC	06-09-2022	06-09-2022	170
24AC	07-09-2022	06-09-2022	330
24AC	08-10-2022	06-09-2022	60

Below is the result that I'm trying to get,  My last 3 Months should be based on Upgade date, If I have an upgrdae date in April, I should consider my last 3 months from 1st Jan till 31st March (April excluded) and My next 3 months should be from 1st April until 30th June. My last and next 3 months average should be as shown in the below table, and my next 3 months (future months) should be based on number of months present in future, If I have data only till October and my upgrade_date is September, I should consider average sales for those 2 months i.e (Sep Sales + Oct Sales)/2. Finally Totals also should be average instead of sum for L3M Avg and N3M Avg

	Store_ID	Upgrade_date	Sales	Last_3_months_(L3M)	Next_3_Months_(N3M)	L3M_Avg	N3M_Avg
	11AB	24-04-2021	1400	500	900	167	300
	24AC	06-09-2022	1900	1150	750	383	375
Total			3300	1650	1650	275	338

any help is appreciated, Thanks

[v-yalanwu-msft · Accepted solution]

Hi, @rookie_963 ;

You could create another measure.

n3m_ave = AVERAGEX(SUMMARIZE('Table',[Store_ID],[Upgrade_date],"1",[N3M_Avg]),[1])

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft]

Hi, @rookie_963 ;

Try this measures:

Last_3_months = 
CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),
                    [Store_ID]=MAX('Table'[Store_ID])
                    &&[Transaction_Date]<=EOMONTH(MAX('Table'[Upgrade_date]),-1)
                    &&[Transaction_Date]>EOMONTH(MAX('Table'[Upgrade_date]),-4)))

Next_3_Months = 
CALCULATE(SUM('Table'[Sales]),
FILTER(ALL('Table'),[Store_ID]=MAX('Table'[Store_ID])
                    &&[Transaction_Date]>EOMONTH(MAX('Table'[Upgrade_date]),-1)
                    &&[Transaction_Date]<=EOMONTH(MAX('Table'[Upgrade_date]),2)))

L3M_Avg = 
var _count=
COUNTROWS(
SUMMARIZE(FILTER('Table',[Store_ID]=MAX('Table'[Store_ID])
        &&[Transaction_Date]<=EOMONTH(MAX('Table'[Upgrade_date]),-1)
        &&[Transaction_Date]>EOMONTH(MAX('Table'[Upgrade_date]),-4)),[Transaction_Date].[Year],[Transaction_Date].[Month]))
return [Last_3_months]/_count

N3M_Avg = 
var _count=
COUNTROWS(
SUMMARIZE(FILTER('Table',[Store_ID]=MAX('Table'[Store_ID])
        &&[Transaction_Date]>EOMONTH(MAX('Table'[Upgrade_date]),-1)
        &&[Transaction_Date]<=EOMONTH(MAX('Table'[Upgrade_date]),2)),
        [Transaction_Date].[Year],[Transaction_Date].[Month]))
return [Next_3_Months]/_count

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rookie_963]

Hi @v-yalanwu-msft 

I really appreciate your help, for the above problem I want average to be computed in Totals 

I currenlty see that totals are based on max date, if I'm not wrong.