Index depending on date, category and first case

[Anonymous]

Hi everybody,

I'm still new in this and need help to index or rank my table.

Here is the situation, I have a table with a date, country, and cases. I want to index/rank by date by country but starting to count by the first appearance.

Below is an example of my data, the Index Column is the desired result, right now I do not have that column. Take US and Mexico as an example, the first case (Case column diff than 0) is on March 13th, so I want that to be the index/rank 1 and then the next day 2, next day 3, and so on but just for the US. Then Mexico the first case (case column diff than 0) is on March 19th, so I want that to be index/rank 1 for Mexico, next day 2, next day 3.

Any ideas, I found a formula using RANKX in combination with EARLIER, but that was fine, but it started counting since the first country appearance, no matter if the cases were 0, I want to start counting when it detects the first case.

Date	Country	Cases	Index
Tuesday, March 10, 2020	Mexico	0
Tuesday, March 10, 2020	US	0
Wednesday, March 11, 2020	Mexico	0
Wednesday, March 11, 2020	US	0
Thursday, March 12, 2020	Mexico	0
Thursday, March 12, 2020	US	0
Friday, March 13, 2020	Mexico	0
Friday, March 13, 2020	US	5	1
Saturday, March 14, 2020	Mexico	0
Saturday, March 14, 2020	US	0	2
Sunday, March 15, 2020	Mexico	0
Sunday, March 15, 2020	US	12	3
Monday, March 16, 2020	Mexico	0
Monday, March 16, 2020	US	29	4
Tuesday, March 17, 2020	Mexico	0
Tuesday, March 17, 2020	US	39	5
Wednesday, March 18, 2020	Mexico	0
Wednesday, March 18, 2020	US	46	6
Thursday, March 19, 2020	Mexico	1	1
Thursday, March 19, 2020	US	78	7
Friday, March 20, 2020	Mexico	1	2
Friday, March 20, 2020	US	83	8
Saturday, March 21, 2020	Mexico	2	3
Saturday, March 21, 2020	US	131	9
Sunday, March 22, 2020	Mexico	2	4
Sunday, March 22, 2020	US	138	10
Monday, March 23, 2020	Mexico	3	5
Monday, March 23, 2020	US	196	11

[Mariusz · Accepted solution]

Hi @Anonymous 

try this 

Column = 
VAR __filterCountry = ALLEXCEPT( 'Table', 'Table'[Country] )
VAR __firstDate =
    CALCULATE(
        MIN( 'Table'[Date] ),
        __filterCountry,
        'Table'[Cases] > 0
    )
VAR __tbl = 
    CALCULATETABLE(
        'Table',
        __filterCountry,
        'Table'[Date] >= __firstDate
    )
RETURN 
IF( 
    'Table'[Date] >= __firstDate, 
    RANKX(
        __tbl,
        'Table'[Date],,
        ASC,
        Dense
    )
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

This did the trick, thank you! Definitely I have more to learn.

[Mariusz]

Hi @Anonymous 

No problem!

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn