Not resetting cumulative total

Hi All,

I need to have the backlog model I build not resetting the cumulative balance (Open Tickets - Resolved Tickets) when the users selected a period.

In the mockup, if I selected a period Running Balance calculation resets, while I need it to consider the Running Balance of the last day before the period selected.

E.g.: In Week 1 the Running Balance is 129, in Week 2 it’s 338, but if I selected just Week 2 I lose Week 1 and Running Balance starts from 209, while it should be 338 (129+209).

I’ve seen many patterns like this resolved in the forum, but I was not able to apply to this.

Thanks for help

Roberto

image

Less important, but need to know why I get a blank value is a slicer I made from the calendar?
image

Backlog.pbix (72.7 KB)

Easy one

Running Balance = 
CALCULATE(
   [Balance], 
    FILTER(
        ALLEXCEPT('Date', 'Date'[Week Number]),
        'Date'[Date] <= max('Date'[Date])
    )
)

Check out this tutorial to learn a bit more about ALLEXCEPT.

Thanks
Sam

@Roberto,

I think if you just tweak your Running Balance measure to use ALLEXCEPT instead of ALLSELECTED, it will give you the result you’re looking for:

Running Balance = 
CALCULATE(
   [Balance], 
    FILTER(
        ALLEXCEPT('Date','Date'[Week Number]),
        'Date'[Date] <= max('Date'[Date])
    )
)

image

This article does a good job explaining the blank value in the slicer problem:
https://exceleratorbi.com.au/rename-blank-in-a-slicer-in-power-bi/

A simple fix is to put an “is not blank” filter on Month Year in your slicer.

image

image

Hope this is helpful.

1 Like

It worked nicely. I commit myself to understand how it works! Thanks a lot!

I’m honestly surprised how powerful DAX can be once you embrace (and understand) it fully.
Enterprise DNA training and support is also essential to reach analytics level I believed unthinkable. Thank you so much!

Quick question - what’s the easiest way to find short topic videos like the ALLSELECTED clip you linked to above that are embedded in larger course topics? I tried searching on ALLSELECTED in the main Enterprise DNA portal, and it didn’t return any results.

Thanks!

The way to search for videos is actually inside the forum search bar.

Unfortunately the portal doesn’t have a global search function, so inside the forum we place links to nearly all videos.

For example

Type ALLSELECTED into the search bar and you’ll see a number of links to Enterprise DNA Online.

Wow, really powerful search function that I totally overlooked. Thanks!