cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Blauezone
Regular Visitor

Cumulated rows

Hi everyone, I'm new in the forum.

Here's my problem. I have this chart.

 

DateValue
01.01.20185
01.01.20185
01.01.20185
01.02.20185
01.03.20185
01.04.20185
01.05.20185
01.06.20185
01.07.20185

 

I am now looking for a measure or a calculated column.
I want the values from the "Value" column to be cumulated in the "Expected" column and everything grouped by month (The grouping by month makes power bi with table, not the measure or calculated columm) 

DateValueExpected
01.01.20181515
01.02.2018520
01.03.2018525
01.04.2018530
01.05.2018535
01.06.2018540
01.07.2018545

 

I have tried so:

Cumulative =
VAR RowDate = Tabelle1[Date]
RETURN
      CALCULATE (
         SUM ( Tabelle1[Value] );
         FILTER (
               Tabelle1;
               Tabelle1[Date] <= RowDate

     )
)

 

but the result was this:


Power BI Cumulative.jpg

 

 

 

 

 

 

 

 

 

 

 

Probably because the first of January occurs several times, in the origignal table.

Thank you for your help. Greetings from Switzerland.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

Re: Cumulated rows

@Blauezone

 

As a MEASURE,you could use

 

Cumulative =
CALCULATE (
    SUM ( Tabelle1[Value] ),
    FILTER ( ALL ( Tabelle1 ), Tabelle1[Date] <= MAX ( Tabelle1[Date] ) )
)

 

Try my new Power BI game Cross the River

View solution in original post

4 REPLIES 4
Super User III
Super User III

Re: Cumulated rows

@Blauezone

 

As a MEASURE,you could use

 

Cumulative =
CALCULATE (
    SUM ( Tabelle1[Value] ),
    FILTER ( ALL ( Tabelle1 ), Tabelle1[Date] <= MAX ( Tabelle1[Date] ) )
)

 

Try my new Power BI game Cross the River

View solution in original post

Super User III
Super User III

Re: Cumulated rows

@Blauezone

 

Your formula is also correct for calculated column

 

But you should choose "don't summarize" instead of deafult "SUM" of the field

Try my new Power BI game Cross the River
Highlighted
v-piga-msft Resident Rockstar
Resident Rockstar

Re: Cumulated rows

Hi @Blauezone,

 

I also agree with the measure formula provided by @Zubair_Muhammad which should close your problem.

 

For you scenario, measure should be better than calculated column.

 

If you have solved you problem, only thing that you'll have to notice, just always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

 

If you still need help, please feel free to ask.

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Blauezone
Regular Visitor

Re: Cumulated rows

Wonderful, it works. Many thanks

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors