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 Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

#### Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!