Forum Discussion
Cumulated rows
Hi everyone, I'm new in the forum.
Here's my problem. I have this chart.
| Date | Value |
| 01.01.2018 | 5 |
| 01.01.2018 | 5 |
| 01.01.2018 | 5 |
| 01.02.2018 | 5 |
| 01.03.2018 | 5 |
| 01.04.2018 | 5 |
| 01.05.2018 | 5 |
| 01.06.2018 | 5 |
| 01.07.2018 | 5 |
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)
| Date | Value | Expected |
| 01.01.2018 | 15 | 15 |
| 01.02.2018 | 5 | 20 |
| 01.03.2018 | 5 | 25 |
| 01.04.2018 | 5 | 30 |
| 01.05.2018 | 5 | 35 |
| 01.06.2018 | 5 | 40 |
| 01.07.2018 | 5 | 45 |
I have tried so:
Cumulative =
VAR RowDate = Tabelle1[Date]
RETURN
CALCULATE (
SUM ( Tabelle1[Value] );
FILTER (
Tabelle1;
Tabelle1[Date] <= RowDate
)
)
but the result was this:
Probably because the first of January occurs several times, in the origignal table.
Thank you for your help. Greetings from Switzerland.
As a MEASURE,you could use
Cumulative = CALCULATE ( SUM ( Tabelle1[Value] ), FILTER ( ALL ( Tabelle1 ), Tabelle1[Date] <= MAX ( Tabelle1[Date] ) ) )
4 Replies
As a MEASURE,you could use
Cumulative = CALCULATE ( SUM ( Tabelle1[Value] ), FILTER ( ALL ( Tabelle1 ), Tabelle1[Date] <= MAX ( Tabelle1[Date] ) ) )Your formula is also correct for calculated column
But you should choose "don't summarize" instead of deafult "SUM" of the field
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
Wonderful, it works. Many thanks
