cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
brunomitsuo Frequent Visitor
Frequent Visitor

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

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:1082150,96351,45
13/03/2018 07:14:126006,98358,385
13/03/2018 07:14:1285344,9403,2810
13/03/2018 07:14:1782125,48428,765
13/03/2018 07:14:199215,92444,6810

 

Many Thanks!!!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
affan Established Member
Established Member

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

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])))

Cumulative total based on two columns.png

 

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

View solution in original post

2 REPLIES 2
Highlighted
affan Established Member
Established Member

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

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])))

Cumulative total based on two columns.png

 

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

View solution in original post

brunomitsuo Frequent Visitor
Frequent Visitor

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

It worked !!

 

Thanks!

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 308 members 2,929 guests
Please welcome our newest community members: