Running sum that stops at colum value

[Wresen]

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 

[Jihwan_Kim · Accepted solution]

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 )

[SpartaBI · Accepted solution]

@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

      

[SpartaBI · Accepted solution]

@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

      

[ghoshabhijeet · Accepted solution]

@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 👍

[Wresen]

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

[SpartaBI · Accepted solution]

@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

      

[Wresen]

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.

[SpartaBI · Accepted solution]

@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

      

[Wresen]

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

Your measure work perfect !!

[SpartaBI]

@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 🙂

[Jihwan_Kim · Accepted solution]

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 )

[AlexisOlson]

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 )
    )