cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pjporrino
Frequent Visitor

14 days cumulative incidence

Hi, 

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

countrydatecumulative cases
A01/01/2010
A02/01/2015
A......
A31/12/205678
B01/01/2040
B... 
B31/12/2013456

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

4 REPLIES 4
pjporrino
Frequent Visitor

Captura de pantalla 2021-02-25 a las 21.01.21.png

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

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. 

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

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 )

v-henryk-mstf_0-1615271858562.png

 

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.

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

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

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

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

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors