Cumulate values on a column and display data by record or date range

[LaM]

Hi,

I am pretty new in Power BI, so I think my question is not that difficult but I can't achieve it...

I have records and have a benefit value for each record.

I want to calculate and cumulate the benefit for this column (from the 1st record until the last one).

I want to get the last column in red 'cumulative benefit', but after reading some posts, I couldn't achieve that.

createdon	crceb_benefit	cumulative benefit
09/11/2020 14:24	110	110
09/11/2020 16:10	-5	105
09/11/2020 16:16	-10	95

Once I will get this data, I would like to display the data with a graph like the following one:

• by records (from the first one to the last one)
• or by date range: november 2020, december 2020, january 2021... then by year...

Can you help me to calculate the last column?

Do you think I can display data as I wanted with the only date field createdon that I get in my table?

Thanks

[v-janeyg-msft]

Hi, @LaM 

It’s my pleasure to answer for you.

According to your description,I think you can create a measure to calculate the desired result.

Like this:

Measure = SUMX(FILTER(ALL('Table'),[date]<=SELECTEDVALUE('Table'[date])),[values])

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[LaM]

Hi @v-janeyg-msft ,

I tried your solution but I must do something wrong because it does'nt work 😓

I tried two things:

1. First I created a measure, but when I put it on the the report tab, and add it on a visualization of type 'Line chart', the graph is empty (I also added the createdon on axis).

I can't see the measure in the data tab as a column (like your printscreen), to check the data calculated, is it normal?

    2.I created a column (to see it in the data tab), and I put the same formula, but it also remains empty.

Can't we move the columns to put this new one near the benefit column? To see the data more easily?

My data columns, so my formula seems like yours:

[v-janeyg-msft · Accepted solution]

Hi, @LaM 

Try like this:

column= SUMX(FILTER(ALL('Table'),[date]<=earlier('Table'[date])),[values])

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.