Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Wresen
Post Patron
Post Patron

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
Solution Supplier
Solution Supplier

@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
Solution Supplier
Solution Supplier

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.