Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Marketeasing
Frequent Visitor

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

NameDateCasesAcumCasesDay
Afghanistan20/02/2020 0:0000
Afghanistan21/02/2020 0:0000
Afghanistan22/02/2020 0:0000
Afghanistan23/02/2020 0:0000
Afghanistan24/02/2020 0:0011
Afghanistan25/02/2020 0:0010
Afghanistan26/02/2020 0:0010
Afghanistan27/02/2020 0:0010
Afghanistan28/02/2020 0:0043
Afghanistan29/02/2020 0:0040
Afghanistan01/03/2020 0:0051
Afghanistan02/03/2020 0:0072
Afghanistan03/03/2020 0:0070
Afghanistan04/03/2020 0:0070
Afghanistan05/03/2020 0:00114
Afghanistan06/03/2020 0:00165
Afghanistan07/03/2020 0:00215
Afghanistan08/03/2020 0:00221
Afghanistan09/03/2020 0:00220
Afghanistan10/03/2020 0:00220
Afghanistan11/03/2020 0:00242
Afghanistan12/03/2020 0:00240
Afghanistan13/03/2020 0:004016
Afghanistan14/03/2020 0:00400
2 ACCEPTED SOLUTIONS
Vvelarde
Community Champion
Community Champion

@Marketeasing 

 

Hi, Try this:

 

1. Add a Index Column

 

2. Add a Custom Column

try #"Added Index"{[Index]}[CasesAcum]-#"Added Index" {[Index]-1}[CasesAcum] otherwise 0

 

Regards

 

Victor

 




Lima - Peru

View solution in original post

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

View solution in original post

6 REPLIES 6
Vvelarde
Community Champion
Community Champion

@Marketeasing 

 

Hi, Try this:

 

1. Add a Index Column

 

2. Add a Custom Column

try #"Added Index"{[Index]}[CasesAcum]-#"Added Index" {[Index]-1}[CasesAcum] otherwise 0

 

Regards

 

Victor

 




Lima - Peru
Anonymous
Not applicable

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.

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

@Marketeasing 

 

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




Lima - Peru

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

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.