Not resetting cumulative total

DAX DAX Calculations
1

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)

2

Easy one

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

image
image1002×542 37.3 KB

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

http://portal.enterprisedna.co/courses/108877/lectures/2248109

Thanks
Sam

3

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

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
4

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

5

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!

6

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!

7

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.

image
image.png1357×855 112 KB

8

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