14 days cumulative incidence

[pjporrino]

Hi, 

I have a table with cumulative cases by country and day:

country	date	cumulative cases
A	01/01/20	10
A	02/01/20	15
A	...	...
A	31/12/20	5678
B	01/01/20	40
B	...
B	31/12/20	13456

I need to calculate in a new column for each row the difference between the number of cases of that date and the number of cases 14 days before; so each row in the new column will have cumulative cases(date) - cumulative cases(date - 14 days). 

Could you please help me to get how to calculate that in a new column?

Thanks a lot

Pedro

[pjporrino]

Sorry because I didn't manage to upload a proper screen capture in the post, but now it's here. Thanks

[v-henryk-mstf]

Hi @pjporrino ,

Try the following formula:

result1 =
VAR cur_data =
    MAX ( Table1[Date] )
VAR cur_amount =
    CALCULATE ( MAX ( Table1[Amount] ), Table1[Date] = cur_data )
VAR pre_amount =
    CALCULATE ( MAX ( Table1[Amount] ), Table1[Date] = cur_data - 14 )
RETURN
    IF (
        cur_data - 14
            <> BLANK (),
        cur_amount - pre_amount,
        MAX ( Table1[Amount] )
    )

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

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

[pjporrino]

Thanks a lot, @v-henryk-mstf, for your reply. I tried your expression, and it doesn't give me any error. Still, it makes the calculation only for the last day and the day 14 days behind (my table was updated as yesterday, and therefore, I get only a value for 28/02/2021 and 14/02/2021, but all the cells in the new column before 14/02/2021 are empty, any idea on what is going on?

Thank you very much,

Best

Pedro

[v-henryk-mstf]

Hi @pjporrino ,

I am sorry for my mistake. I did the test again, the reference is as follows:

Result = 
VAR _14daysago = Table1[Date] - 14
VAR _amountfor14dg =
    CALCULATE (
        SUM ( 'Table1'[Amount] ),
        FILTER ( ALL ( 'Table1' ), 'Table1'[Date] = _14daysago )
    )
RETURN
    IF ( ISBLANK ( _amountfor14dg ), BLANK (), 'Table1'[Amount] - _amountfor14dg )

Here is the sample pbix file link.

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

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