cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Wresen
Helper V
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 
running.PNG
 
4 ACCEPTED SOLUTIONS
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

 

Untitled.png

 

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.


Go to My LinkedIn Page


View solution in original post

SpartaBI
Community Champion
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:

SpartaBI_0-1653124058087.png


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png

Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

View solution in original post

SpartaBI
Community Champion
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)

 




2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png

Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

View solution in original post

ghoshabhijeet
Responsive Resident
Responsive Resident

@Wresen  Here is my solution to your problem.  You can try this too.

ghoshabhijeet_0-1653153431981.png

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 👍

 

View solution in original post

9 REPLIES 9
ghoshabhijeet
Responsive Resident
Responsive Resident

@Wresen  Here is my solution to your problem.  You can try this too.

ghoshabhijeet_0-1653153431981.png

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 👍

 

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

SpartaBI
Community Champion
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:

SpartaBI_0-1653124058087.png


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png

Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

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

 




2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png

Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

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

Your measure work perfect !!

 

SpartaBI
Community Champion
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 🙂

Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

 

Untitled.png

 

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.


Go to My LinkedIn Page


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

 

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

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