Summarizing COVID cases by day one day two and so on for each countries

[Anonymous]

Hi , 
I want the cases count to be displayed for day one and day two for each country 

for eg. country A day one is 01/01/2021 ,whereas for country B its 01/04/2021. 

refer to I/p and desired O/P below

I/P
Date	Country	Count
01/01/2021	A	1000
01/02/2021	A	290
01/03/2021	A	200
01/04/2021	A	500
01/02/2021	B	50
01/03/2021	B	0
01/04/2021	B	250
01/04/2021	C	105
O/P
	A	B	C
Day 1	1000	50	105
Day 2	290	0	0
Day 3	200	250	0
Day 4	500	0	0

[v-zhangti · Accepted solution]

Hi, @Anonymous 

You can try the following methods.

Column:

Days = 
CALCULATE (
    COUNT ( 'Table'[Date] ),
    FILTER (
        'Table',
        [Country] = EARLIER ( 'Table'[Country] )
            && [Date] <= EARLIER ( 'Table'[Date] )
    )
)

Then add the matrix view to get the results you expect.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-zhangti 

Thanks for soln. It works. But I need a measure. I am not allowed to add a calculated column. Is there a way to achieve this thru measure? Thanks in advance

[v-zhangti]

Hi, @Anonymous 

Measure:

Days 2 = 
CALCULATE (
    COUNT ( 'Table'[Date] ),
    FILTER (
        ALL('Table'),
        [Country] = SELECTEDVALUE( 'Table'[Country] )
            && [Date] <= SELECTEDVALUE( ( 'Table'[Date] )
    )
))

This is the same as the result of calculating the column. But measure cannot be put inside Rows in matrix view.

Best Regards

[amitchandak]

@Anonymous , You have to create a new column and use that in visual

Day = datediff(Min(Table[Date]), Table[Date], day) +1

[Anonymous]

Hi , This doesnot rank days for each country.

I have added what should the day no look like in annotation