cancel
Showing results for
Did you mean:
Frequent Visitor

## 14 days cumulative incidence

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).

Thanks a lot

Pedro

4 REPLIES 4
Frequent Visitor

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

Community Support

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.

Frequent Visitor

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

Community Support

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.

Announcements

#### The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

#### Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!