cancel
Showing results for
Did you mean:  Helper V

## Running sum that stops at colum value

Hi

I have a running sum that works.

Running sum = CALCULATE( SUMX(filter(ALLNOBLANKROW('Sheet1'[Day]),'Sheet1'[Day]
<= MAX('Sheet1'[Day])), calculate(sum(Sheet1[Value]))))

I would like to modife this so it stops the "running calculation" at day 10.
I have tried modify the measure but it only gives me the total value , not a runnig up to day 10

Running sum stop at day10 = CALCULATE( SUMX(filter(ALLNOBLANKROW('Sheet1'[Day]),'Sheet1'[Day]
<= (10)), CALCULATE( sum(Sheet1[Value]))))

Is this possible to do ?
Thanks so much 4 ACCEPTED SOLUTIONS  Super User

Hi,

Please check the below picture and the attached pbix file. ``````Running sum stop at day10 measure: =
VAR _condition =
MAX ( Data[Day] ) <= 10
RETURN
COALESCE (
CALCULATE (
SUM ( Data[Value] ),
FILTER ( ALL ( Data ), Data[Day] <= MAX ( Data[Day] ) )
),
""
)
* DIVIDE ( _condition, _condition )
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.  Community Champion

@Wresen before I write the measure, not sure why you wrote the original that works the way you wrote it. Please see if this version also works for you:

``````Running Sum 2 =
CALCULATE(
SUM(Sheet1[Value]),
'Sheet1'[Day] <= MAX('Sheet1'[Day])
)        ``````

For the measure that stops at 10:

``````Running Sum Stop at 10 =
IF(
SELECTEDVALUE(Sheet1[Day]) > 10 || NOT HASONEVALUE(Sheet1[Day]),
CALCULATE(
SUM(Sheet1[Value]),
'Sheet1'[Day] <= 10
),
[Running Sum 2]
)        ``````

Wasn't sure how you want to handle the values after 10 (blank or continue to be 19). Same for the total. I made it to look like this, but no problem to modify it differenly:  Showcase Report – Contoso By SpartaBI  Community Champion

@Wresen my pleasure.
When you write:
Measure = Calculate( [a measure] , Month[month] = Max(Sales[month]))
Then Max(Sales[month]) is equal the max in the current filter context.
That's mean that if you are on a row where it's Jan 2022 it will be Jan 2022 and not the max of your date.
To achive the max of all dates you will write something like
CALCULATE(Max(Sales[month]), REMOVEFILTERS('Sheet1')
You will also better in any case to use varibales, so this will be your measure:

``````Measure =
VAR _max_month_all_sales = CALCULATE(Max(Sales[month]), REMOVEFILTERS('Sheet1'))
RETURN
Calculate( [a measure] , Month[month] = _max_month_all_sales)`````` Showcase Report – Contoso By SpartaBI  Responsive Resident

@Wresen  Here is my solution to your problem.  You can try this too. `Running sum till Day 10 = IF (     MAX ( 'DataTable'[Day] ) <= 10,     CALCULATE (         SUM('DataTable'[Value]),         FILTER (             ALL ( 'DataTable' ),             ISONORAFTER ( 'DataTable'[Day], MAX ( 'DataTable'[Day] ), DESC )             && 'DataTable'[Day] <= 10         )     ),     BLANK ())`

 ** If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution, so that it comes in top of the search and help others. Thank you !Good Luck 👍

9 REPLIES 9  Responsive Resident

@Wresen  Here is my solution to your problem.  You can try this too. `Running sum till Day 10 = IF (     MAX ( 'DataTable'[Day] ) <= 10,     CALCULATE (         SUM('DataTable'[Value]),         FILTER (             ALL ( 'DataTable' ),             ISONORAFTER ( 'DataTable'[Day], MAX ( 'DataTable'[Day] ), DESC )             && 'DataTable'[Day] <= 10         )     ),     BLANK ())`

 ** If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution, so that it comes in top of the search and help others. Thank you !Good Luck 👍  Helper V

Hi @ghoshabhijeet
That also works excaktly as i want.
Thanks so much  Community Champion

@Wresen before I write the measure, not sure why you wrote the original that works the way you wrote it. Please see if this version also works for you:

``````Running Sum 2 =
CALCULATE(
SUM(Sheet1[Value]),
'Sheet1'[Day] <= MAX('Sheet1'[Day])
)        ``````

For the measure that stops at 10:

``````Running Sum Stop at 10 =
IF(
SELECTEDVALUE(Sheet1[Day]) > 10 || NOT HASONEVALUE(Sheet1[Day]),
CALCULATE(
SUM(Sheet1[Value]),
'Sheet1'[Day] <= 10
),
[Running Sum 2]
)        ``````

Wasn't sure how you want to handle the values after 10 (blank or continue to be 19). Same for the total. I made it to look like this, but no problem to modify it differenly:  Showcase Report – Contoso By SpartaBI  Helper V

Hi @Jihwan_Kim  and @SpartaBI

Thanks so much for the help and the measures that i will look at and see, both works excaktly how i want it.

I have one more question maybe not relatedted to the running sum but i will try here anyway.

Why does not this work

Measure = Calculate( [a measure] , Month[month] = Max(Month[month]))

But this works

Measure = Calculate( [a measure] , Month[month] = "202112")

(i want the measure [a measure] to be filtered to only show the last value in the month)

Thanks so much.  Community Champion

@Wresen my pleasure.
When you write:
Measure = Calculate( [a measure] , Month[month] = Max(Sales[month]))
Then Max(Sales[month]) is equal the max in the current filter context.
That's mean that if you are on a row where it's Jan 2022 it will be Jan 2022 and not the max of your date.
To achive the max of all dates you will write something like
CALCULATE(Max(Sales[month]), REMOVEFILTERS('Sheet1')
You will also better in any case to use varibales, so this will be your measure:

``````Measure =
VAR _max_month_all_sales = CALCULATE(Max(Sales[month]), REMOVEFILTERS('Sheet1'))
RETURN
Calculate( [a measure] , Month[month] = _max_month_all_sales)`````` Showcase Report – Contoso By SpartaBI  Helper V

@SpartaBI
Thanks so much for the solution and explination on why i did not work.  Community Champion

@Wresen my pleasure 🙂
Hey, check out my showcase report:
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543
Give it a thumbs up if you liked it 🙂  Super User

Hi,

Please check the below picture and the attached pbix file. ``````Running sum stop at day10 measure: =
VAR _condition =
MAX ( Data[Day] ) <= 10
RETURN
COALESCE (
CALCULATE (
SUM ( Data[Value] ),
FILTER ( ALL ( Data ), Data[Day] <= MAX ( Data[Day] ) )
),
""
)
* DIVIDE ( _condition, _condition )
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.  Super User

I'd suggest a simplification of this:

``````Running sum stop at day10 =
VAR _condition = MAX ( Data[Day] ) <= 10
RETURN
CALCULATE (
SUM ( Data[Value] ),
FILTER ( ALL ( Data[Day] ), Data[Day] <= MAX ( Data[Day] ) && _condition )
)``````

Or a slight refactoring:

``````Running sum stop at day10 =
VAR _currday = MAX ( Data[Day] )
RETURN
CALCULATE (
SUM ( Data[Value] ),
FILTER ( ALL ( Data[Day] ), Data[Day] <= _currday && _currday <= 10 )
)`````` Announcements The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform. #### Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison! #### The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022. Top Solution Authors
Top Kudoed Authors
Users online (4,063)