Forum Discussion
Cumulative values & Diagram view
Hello,
I have the following problem:
I have created a chart with cumulative values and the line chart should only go up to the current week and not continue with the values from April to December.
Can anyone help me?
Thank you!
Hi Bedlo_82
You can calculate the max date with value and create a condition to return the cumulative value only when it is <= the max date
Cumulative Value = VAR _MaxDate = CALCULATE ( LASTNONBLANK ( 'Table'[Date], [Sum of Value] ), REMOVEFILTERS ( 'Table' ) ) RETURN IF ( MAX ( 'Table'[Date] ) <= _MaxDate, CALCULATE ( [Sum of Value], FILTER ( ALL ( 'Table'[Date] ), 'Table'[Date] <= MAX ( 'Table'[Date] ) ) ) )Please see the attached sample pbix.
Hi Bedlo_82, if you're new to PowerBI, you might start by using visual calculations. Read more about it here - Using visual calculations in Power BI Desktop - Power BI | Microsoft Learn
In the example below I use runningsum() fucntion to get a cummulative value. Then I use IF statement to show the result only when Date at a row level is smaller than the one in variable _MaxDate. You can change the value in this variable. When working with visual calucaltion we'll need to add a field based on which you calculate (i.e., Sales), but the if you don't want to show it, you can hide it.
Attached you'll find a pbix file for to learn and explore this apporach.Good luck! 🙂
Hi Bedlo_82 ,
It sounds like you're working with a cumulative line chart and want the line to display data only up to the current week, without projecting or extending the line through the rest of the year (April to December). To achieve this, you’ll need to ensure that your dataset only includes data up to the current week, or that future values (from April onward) are either removed or set to null so that the chart doesn't plot them.Most charting tools or BI platforms (like Power BI, Tableau, Excel, etc.) recognize null or blank values and will stop the line at the last available data point. If you're using a date field to plot the X-axis, make sure it’s filtered dynamically to show only data up to the current date or week.
14 Replies
- mdaatifraza5556
Super User
Hi Bedlo_82
Can you please provide some samples?
Or you can try this below concept
VAR CurrentWeek = WEEKNUM(TODAY()) --- Store CurrentWeek
RETURN
CALCULATE(
[Measure(or your values)],
FILTER(
ALL(Table),
Table[WeekNumber] <= CurrentWeek --- In your table should have WeekNumber
)
)
If this answers your questions, Kindly accept it as a solution.I have attached a simple example.
It shows the current situation.
I would like:
The diagram should stop at “the red mark” and not go with the values from April to December.Thank you!
- grazitti_sapna
Hi Bedlo_82 ,
Please try using:-
Cumulative Sales =
VAR CurrentWeek = WEEKNUM(TODAY())
RETURN
CALCULATE(
SUM(Sales[SalesAmount]),
FILTER(
ALLSELECTED(Sales[Date]),
Sales[WeekNumber] <= CurrentWeek
)
)-
This ensures that data is only accumulated up to the current week.
2. Use this measure in your line chart
-
Replace your existing cumulative measure with this new one.
🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!
Unfortunately, I am a beginner in Power BI.
I have now entered the formula like this:
VAR CurrentWeek = WEEKNUM(TODAY(25.03.2025))
Now it won't let me continue
- mdaatifraza5556
HI Bedlo_82
inside the TODAY don't enter the date
Keep it only like this
VAR CurrentWeek = WEEKNUM(TODAY())
Today() -- It will fetch the todays date automatically no need of entering date inside it.
-
- danextian
Hi Bedlo_82
You can calculate the max date with value and create a condition to return the cumulative value only when it is <= the max date
Cumulative Value = VAR _MaxDate = CALCULATE ( LASTNONBLANK ( 'Table'[Date], [Sum of Value] ), REMOVEFILTERS ( 'Table' ) ) RETURN IF ( MAX ( 'Table'[Date] ) <= _MaxDate, CALCULATE ( [Sum of Value], FILTER ( ALL ( 'Table'[Date] ), 'Table'[Date] <= MAX ( 'Table'[Date] ) ) ) )Please see the attached sample pbix.
- Sergii24
Hi Bedlo_82, if you're new to PowerBI, you might start by using visual calculations. Read more about it here - Using visual calculations in Power BI Desktop - Power BI | Microsoft Learn
In the example below I use runningsum() fucntion to get a cummulative value. Then I use IF statement to show the result only when Date at a row level is smaller than the one in variable _MaxDate. You can change the value in this variable. When working with visual calucaltion we'll need to add a field based on which you calculate (i.e., Sales), but the if you don't want to show it, you can hide it.
Attached you'll find a pbix file for to learn and explore this apporach.Good luck! 🙂
rohit1991Hi Bedlo_82 ,
It sounds like you're working with a cumulative line chart and want the line to display data only up to the current week, without projecting or extending the line through the rest of the year (April to December). To achieve this, you’ll need to ensure that your dataset only includes data up to the current week, or that future values (from April onward) are either removed or set to null so that the chart doesn't plot them.Most charting tools or BI platforms (like Power BI, Tableau, Excel, etc.) recognize null or blank values and will stop the line at the last available data point. If you're using a date field to plot the X-axis, make sure it’s filtered dynamically to show only data up to the current date or week.
