Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
As a MEASURE,you could use
Cumulative = CALCULATE ( SUM ( Tabelle1[Value] ), FILTER ( ALL ( Tabelle1 ), Tabelle1[Date] <= MAX ( Tabelle1[Date] ) ) )
Wonderful, it works. Many thanks
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |