Cumulative Sum (reference hh:mm:ss) of same time

[brunomitsuo]

Hello everyone,

In this table, column "Single Value" is a value that i received from a Line Number (in this case mostly 10) in a date and hour. "Cumulative Value" is a calculated column for a cumulative sum.

Everything is ok until time is 07:14:12. I got a problem because I received a value from Line Number 5 and 10 at same time, and sum keeps 403,28 twice but it should be "first" 351,40 + 6,98 = 358,38 and "after" 358,38 + 44,90 = 403,28

As calculated column dax is:

Cumulative Value = CALCULATE(SUM(table[Single Value]);FILTER(table;table[Date Hour]<=EARLIER(table[Date Hour])))

How it should be:

13/03/2018 07:14:10	821	50,96	351,4	5
13/03/2018 07:14:12	600	6,98	358,38	5
13/03/2018 07:14:12	853	44,9	403,28	10
13/03/2018 07:14:17	821	25,48	428,76	5
13/03/2018 07:14:19	92	15,92	444,68	10

Many Thanks!!!

[affan · Accepted solution]

Hi @brunomitsuo

You can add an other column as below:

Hour_Line = 
VALUE(Table1[Date Hour])+(Table1[Line Number]/1000000)

Then use this column to filter in the Cumulative

Cumulative Value = CALCULATE(SUM(table1[Single Value]),FILTER(Table1,Table1[Hour_Line]<=EARLIER(table1[Hour_Line])))

You can download the sample pbix here

If this helped you, please mark this post as an accepted solution and like to give KUDOS .

Regards,

Affan

[brunomitsuo]

It worked !!

Thanks!