Sum sales same days different year

[jaimeeduardo_gc]

Hi everybody, i have this data that represents my sales for different dates from last year and this year:

Date	Id_store	Sales
2019-07-01	1	50
2019-07-02	1	10
2019-07-03	1	25
2019-07-04	1	23
2019-07-05	1	457
2019-07-06	1	42
2020-07-02	1	20
2020-07-04	1	100
2020-07-06	1	38
2019-07-01	2	23
2019-07-02	2	423
2019-07-03	2	65
2019-07-04	2	8765
2019-07-05	2	34
2019-07-06	2	123
2019-07-07	2	346
2019-07-08	2	54
2019-07-09	2	45
2019-07-10	2	54
2020-07-01	2	200
2020-07-11	2	167
2019-07-01	3	234
2020-07-13	3	643

I need to sum only sales of last year in the same store, same days and same month of  i had sales this year, something like this:

Id_store	Sales 2019	Sales 2020
1	75	158
2	23	367
3	0	643

*CURRENT YEAR OR SELECTED YEAR IS THE MANDATORY

For example:

in id_store =  1 and year 2019 only sum sales of days: 2, 4 and 6 because in the current year (2020) I just have sales these days.

in id_store = 2 and year 2019 only sum sales of day: 1 because is the only day with sales in 2020 that i had sales in 2019.

in id_store= 3 and year 2019 there is no days equals to current year so the sum of sales in 2019 is 0 but in 2020 is 643.

I´m trying using P() function but i'm not getting the expected result.

I attach an example of what i'm trying to do.

I would be very grateful if someone could help me or suggest another type of solution. Thank you very much everyone for the help. 

[sunny_talwar · Accepted solution]

Try this by creating two new fields in the script

Sales:
LOAD Date,
	 year(Date) as year,
	 day(Date) as day,
	 num(month(Date)) as month,
	 id_store,
	 sales,
	 id_store&'|'&Num(Date) as CurrentYearKey,
	 id_store&'|'&Num(AddYears(Date, -1)) as PreviousYearKey;
LOAD * INLINE [
    Date, id_store, sales
    2019-07-01, 1, 50
    2019-07-02, 1, 10
    2019-07-03, 1, 25
    2019-07-04, 1, 23
    2019-07-05, 1, 457
    2019-07-06, 1, 42
    2020-07-02, 1, 20
    2020-07-04, 1, 100
    2020-07-06, 1, 38
    2019-07-01, 2, 23
    2019-07-02, 2, 423
    2019-07-03, 2, 65
    2019-07-04, 2, 8765
    2019-07-05, 2, 34
    2019-07-06, 2, 123
    2019-07-07, 2, 346
    2019-07-08, 2, 54
    2019-07-09, 2, 45
    2019-07-10, 2, 54
    2020-07-01, 2, 200
    2020-07-11, 2, 167
    2020-07-01, 3, 234
    2020-07-13, 3, 643
];

and then use this expression for Last Year

Sum({<CurrentYearKey = p(PreviousYearKey), year>} sales)

[jaimeeduardo_gc]

thanks a lot. It works!!! 😄