Forum Discussion
How can I calculate an increment between two accumulated values?
Hello, I have to calculate a new column CasesDay (red color) as the variation of the accumulated column CasesAcum
Thanks in advance
| Name | Date | CasesAcum | CasesDay |
| Afghanistan | 20/02/2020 0:00 | 0 | 0 |
| Afghanistan | 21/02/2020 0:00 | 0 | 0 |
| Afghanistan | 22/02/2020 0:00 | 0 | 0 |
| Afghanistan | 23/02/2020 0:00 | 0 | 0 |
| Afghanistan | 24/02/2020 0:00 | 1 | 1 |
| Afghanistan | 25/02/2020 0:00 | 1 | 0 |
| Afghanistan | 26/02/2020 0:00 | 1 | 0 |
| Afghanistan | 27/02/2020 0:00 | 1 | 0 |
| Afghanistan | 28/02/2020 0:00 | 4 | 3 |
| Afghanistan | 29/02/2020 0:00 | 4 | 0 |
| Afghanistan | 01/03/2020 0:00 | 5 | 1 |
| Afghanistan | 02/03/2020 0:00 | 7 | 2 |
| Afghanistan | 03/03/2020 0:00 | 7 | 0 |
| Afghanistan | 04/03/2020 0:00 | 7 | 0 |
| Afghanistan | 05/03/2020 0:00 | 11 | 4 |
| Afghanistan | 06/03/2020 0:00 | 16 | 5 |
| Afghanistan | 07/03/2020 0:00 | 21 | 5 |
| Afghanistan | 08/03/2020 0:00 | 22 | 1 |
| Afghanistan | 09/03/2020 0:00 | 22 | 0 |
| Afghanistan | 10/03/2020 0:00 | 22 | 0 |
| Afghanistan | 11/03/2020 0:00 | 24 | 2 |
| Afghanistan | 12/03/2020 0:00 | 24 | 0 |
| Afghanistan | 13/03/2020 0:00 | 40 | 16 |
| Afghanistan | 14/03/2020 0:00 | 40 | 0 |
Hi, Try this:
1. Add a Index Column
2. Add a Custom Column
try #"Added Index"{[Index]}[CasesAcum]-#"Added Index" {[Index]-1}[CasesAcum] otherwise 0Regards
Victor
Finally I've created a new formula to detect the change of name and that's solved
= Table.AddColumn(#"Índice agregado", "Cases", each if #"Índice agregado"{[Índice]}[#"Name"] = #"Índice agregado"{[Índice]-1}[#"Name"] then #"Índice agregado"{[Índice]}[#"CasesAcum"]-#"Índice agregado"{[Índice]-1}[#"CasesAcum"] else 0)
Thank you Victor
6 Replies
- Vvelarde
Community Champion
Hi, Try this:
1. Add a Index Column
2. Add a Custom Column
try #"Added Index"{[Index]}[CasesAcum]-#"Added Index" {[Index]-1}[CasesAcum] otherwise 0Regards
Victor
Hello victor, thank you for your answer, but there are more than one value in the column "Name", and I need to create an index for each "Name" value. Do you know how to do that? Thank you again
Finally I've created a new formula to detect the change of name and that's solved
= Table.AddColumn(#"Índice agregado", "Cases", each if #"Índice agregado"{[Índice]}[#"Name"] = #"Índice agregado"{[Índice]-1}[#"Name"] then #"Índice agregado"{[Índice]}[#"CasesAcum"]-#"Índice agregado"{[Índice]-1}[#"CasesAcum"] else 0)
Thank you Victor
Hello Victor, I've tried the expression that you said but I have big problems with the performance when is loading data.
This solition is non-viable.
Do you know how to create the same column from DAX? because woth powerquery doesn't work.
I don't know why bit to access the rows through the index position is very slow.
Thank toy in advance
- Vvelarde
Try with this Dax Calculated Column:
Mycolumn = 'Table'[CasesAcum] - CALCULATE ( FIRSTNONBLANK ( 'Table'[CasesAcum]; 'Table'[CasesAcum] ); TOPN ( 1; FILTER ( 'Table'; 'Table'[Name] = EARLIER ( 'Table'[Name] ) && 'Table'[Date] < EARLIER ( 'Table'[Date] ) ); 'Table'[Date]; DESC ) )Or
Mycolumn = 'Table'[CasesAcum] - CALCULATE ( LASTNONBLANK ( 'Table'[CasesAcum]; 'Table'[CasesAcum] ); FILTER ( 'Table'; 'Table'[Name] = EARLIER ( 'Table'[Name] ) && 'Table'[Date] < EARLIER ( 'Table'[Date] ) ) )Regards
Victor
Hi Vvelarde,
Thanks for your answer.
However, If I run the code as you wrote, all the results will shift up for 1 row. We can see that the first row has value, while the last row is null. The target answer should be an empty value in the first row with a value in the last row.
But I think the logic is perfect here, I don't what costs this problem.
