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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Zubair_Muhammad
Community Champion
Community Champion

@Blauezone

 

As a MEASURE,you could use

 

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

 


Regards
Zubair

Please try my custom visuals

View solution in original post

4 REPLIES 4
Blauezone
Regular Visitor

Wonderful, it works. Many thanks

v-piga-msft
Resident Rockstar
Resident Rockstar

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.
Zubair_Muhammad
Community Champion
Community Champion

@Blauezone

 

As a MEASURE,you could use

 

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

 


Regards
Zubair

Please try my custom visuals

@Blauezone

 

Your formula is also correct for calculated column

 

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


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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