Calculate Monthly Average Sales Before and After Retail Store Relocation

[Kratos_ZA]

Hi Friends,

Kindly assist with the below.

I have a few stores that have relocated/moved from one location to another. I would like to calculate the average sales before vs after the relocation. The issue is that the stores have different relocation dates: eg: Store 1 relocated on 2021-02-01 so i would calculate sales 12 months prior to 2021-02-01 and 12 months after 2021-02-01 an

The problem is that the dates are different for each store so i am unable to calculate the before and after sales average to see if there was a growth. Is there anyway around this instead of calculating manually in excel?

Store Name	Relocation Date
Store 1	2021-02-01
Store 2	2021-06-01
Store 3	2021-09-01
Store 4	2022-11-01
Store 5	2022-02-01

SALES DONE
Store Name	2020-01-01	2020-02-01	2020-03-01	2020-04-01	2020-05-01	2020-06-01	2020-07-01	2020-08-01	2020-09-01	2020-10-01	2020-11-01	2020-12-01
Store 1	32	54	34	656	456	4	5	6	456	547	5757	67
Store 2	4	43	5	665	5	4334	45	435	3	3	3	4
Store 3	34	435	65	75	34	54	3	6	5	35	45	45
Store 4	45	345	45	5	34	35	345	5435	34	435	45	56
Store 5	54	3	44	53	45	565	768	87	345	23	45	4

[amitchandak]

@Kratos_ZA , Try measure like

after 12 =
var _min = calculate(Min(Store[Relocation Dat]), allexcept(Store, Table[Store]))
var _max = date(year(_min), Month(_max)+12, Day(_min))
return
calculate(sum(Table[Amount]) , filter(Table, Table[date] <=_max && [date] >=_min))

or

after 12 =
var _min = calculate(Min(Store[Relocation Dat]), allexcept(Store, Table[Store]))

return

CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],_min ,12,MONTH))

before 12 =
var _max = calculate(Min(Store[Relocation Dat]), allexcept(Store, Table[Store]))
var _max = date(year(_min), Month(_max)-12, Day(_min))
return
calculate(sum(Table[Amount]) , filter(Table, Table[date] <=_max && [date] >=_min))

or

before 12 =
var _min = calculate(Min(Store[Relocation Dat]), allexcept(Store, Table[Store]))

return

CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],_min ,-12,MONTH))