Sum of value by date year ago and filtered by another key

[a1enas]

HI!

I have the same data in table:

DATE	NUMBER	ID	SUM
01.01.2018	1	1	550
01.01.2018	1	1011	1000
01.01.2018	2	4	1500
01.01.2018	3	3	200
02.01.2018	2	4	700
02.01.2018	2	204	600
03.01.2018	3	3	300
...	...	...	...

and I need to calculate the total sum for every NUMBER by DATE year ago. How i can do it?

I created column with _PREV_DATE and tried to use: 

=calculate(

SUM(table[SUM);

        filter(table;

table[DATE]=table[_PREV_DATE] && table[NUMBER]=table[NUMBER]))

but it failed (blank), what am i doing wrong?

so i want something like this (may be it can be measure, not calculated column):

DATE	NUMBER	ID	SUM	SUM YEAR AGO
01.01.2018	1	1	550	800
01.01.2018	1	1011	1000	800
01.01.2018	2	4	1500	600
01.01.2018	3	3	200	1500
02.01.2018	2	4	700	750
02.01.2018	2	204	600	750
03.01.2018	3	3	300	100

[Ashish_Mathur]

Hi,

Ensure that the entreis in the Date column are actual date entries (so replace . with /).  Create a Calendar Table and build a relationship from the Date column of the Data Table with the Date column of the Calendar Table.  In the Calendar Table, extract Year and Months using the following calculated column formulas.  Year = Year(Calendar[Date]) and Month = FORMAT(Calendar[Date],"mmmm").  To your visual, drag Year and Month from the Calendar Table and Number, ID from the Data Table.  Write the following measures

Total = SUM(Data[Sum])

Total LY = CALCULATE([Total],PREVIOUSYEAR(Calendar[Date])

Hope this helps.

[v-cherch-msft · Accepted solution]

Hi @a1enas 

You may create a measure like below:

Measure = 
VAR _pre =
    DATE ( YEAR ( MAX ( Table1[Date] ) ) - 1, MONTH ( MAX ( Table1[Date] ) ), DAY ( MAX ( Table1[Date] ) ) )
RETURN
    CALCULATE (
        SUM ( Table1[SUM] ),
        FILTER (
            ALL ( Table1 ),
            Table1[Date] = _pre
                && Table1[Number] = MAX ( Table1[Number] )
        )
    )

Regards,