Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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])
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.
@j_martinho , Try a new column
sumx(filter(Table, [CityCode] = earlier( [CityCode] ) && [CityCode] <= earlier( [Date] ) && [CityCode] >= earlier( [Date] )-14),[Total Cases])
@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])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
81 | |
70 | |
62 |
User | Count |
---|---|
148 | |
116 | |
104 | |
90 | |
65 |