Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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 )
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.