Sum total column values from the last 14 days

[j_martinho]

Hello!

I'm trying to create a new column (DAX or PowerQuery) so that it adds the value of the Cases column for the last 14 days.

The sum must take into account the CityCode. Thus, adding the last 14 days for each CityCode.

Can you help me?

Data example:

Date	Cases	CityCode	Total Cases Last 14 days
10/08/2020	3012	53	29737
09/08/2020	1233	53	26725
08/08/2020	1921	53	25492
07/08/2020	1903	53	23571
06/08/2020	2034	53	21668
05/08/2020	2042	53	19634
04/08/2020	1880	53	17592
03/08/2020	2016	53	15712
02/08/2020	2106	53	13696
01/08/2020	1630	53	11590
31/07/2020	1850	53	9960
30/07/2020	2100	53	8110
29/07/2020	1616	53	6010
28/07/2020	2246	53	4394
27/07/2020	2148	53	2148
10/08/2020	4012	11	44737
09/08/2020	2233	11	40725
08/08/2020	2921	11	38492
07/08/2020	2903	11	35571
06/08/2020	3034	11	32668
05/08/2020	3042	11	29634
04/08/2020	2880	11	26592
03/08/2020	3016	11	23712
02/08/2020	3106	11	20696
01/08/2020	2630	11	17590
31/07/2020	2850	11	14960
30/07/2020	3100	11	12110
29/07/2020	2616	11	9010
28/07/2020	3246	11	6394
27/07/2020	3148	11	3148

[v-diye-msft]

Hi @j_martinho 

Would you like to get the sum results of [Total Cases Last 14 days] by [Citycode]? You can try below calculated column:

Column = CALCULATE(SUM('Table (2)'[Total Cases Last 14 days]),FILTER(ALL('Table (2)'),[CityCode]=EARLIER('Table (2)'[CityCode])))

If not help. please kindly elaborate more.

[amitchandak]

@j_martinho , Try a new column

sumx(filter(Table, [CityCode] = earlier( [CityCode] ) && [CityCode] <= earlier( [Date] ) && [CityCode] >= earlier( [Date] )-14),[Total Cases])

[j_martinho · Accepted solution]

@amitchandak I made two adjustments and solved it. Thank you!

Casos Ativos = 

sumx(
    filter(NOVO_PROTO,
            NOVO_PROTO[city_ibge_code] = earlier( NOVO_PROTO[city_ibge_code] )
             && NOVO_PROTO[Data] <= earlier( NOVO_PROTO[Data] )
             && NOVO_PROTO[Data] >= earlier( NOVO_PROTO[Data] )-13),

    NOVO_PROTO[Novos Confirmados])

[j_martinho]

@amitchandak Thank you. But the column went blank.