Running total not working as expected

[DavidWaters100]

Hi,

I am trying to return running total in the following measure.  My "main table" is joined to a "Dev Months" table which contains all month numbers, whereas the main table can contains gaps, depending on what slicer is selected by a user, so this solution allows all months to show in the table even if they are not in a particular selection.  However as per below, the running total does not work!  I'm wondering if anyone has a solution to offer please?  I'm thinking I need to add in a return last non-blank, perhaps?

thanks!

Premium running total =

CALCULATE(

    SUM('Main Table'[Total]),

    FILTER(

        ALLSELECTED('Dev months'[Dev Month]),

        ISONORAFTER('Dev months'[Dev Month], MAX('Main Table'[Development Month ]), DESC)

    ))

[MFelix]

Hi @DavidWaters100 

Can you please share a mockup data or sample of your PBIX file if the information is sensitive please share it trough private message.

Please see this post regarding How to Get Your Question Answered Quickly (courtesy of @Greg_Deckler) and How to provide sample data in the Power BI Forum (courtesy of @ImkeF).

[DavidWaters100]

Hi @MFelix 

OK thanks, will look to produce a mock-up.  The problem has evolved to become: how to stop the values when the max that exists for each year in the data is reached - 9 in this case (Dev month is a stand-alone joined table here)

[MFelix · Accepted solution]

Hi @DavidWaters100 ,

Believe that your filtering is incorrect because of the way you are using the MAX.

Since you have a relationship between both tables when you use the syntax  

 ISONORAFTER('Dev months'[Dev Month], MAX('Main Table'[Development Month ])

Basically you are picking up the values even if there aren't any data believe that you need to use something similar to:

FILTER (DevMonth; DevMonth[Month] <= MAXX(ALL(MainTable[Development Month]);MainTable[Development Month]))

Be aware that I have writen this by head, did not make any test with any data.

[DavidWaters100]

Hi @MFelix - thanks, I think you have the correct solution as I just tried and it stops at the correct Dev month - I have messed up the running total but will correct it. 

I also found another method which was just to put the below if statement in before the calulation, feels like a cheat using the average - your solution is better/proper way!

if(average(DevMonth[Month])>max('Main Table'[Development Month]),0, - otherwise the calculation

[MFelix]

@DavidWaters100 ,

Believe that the problem of this is not the use of the average but of the IF statment, that for filtering information can return some stranges results, especially when you make the filter context transition to the total values.

But glad you where abble to figure out.

[DavidWaters100]

update - just realised if I change the Max to 'Dev months'[Dev Month]), it does work.

However the Max is there to stop values returning when the dev month gets too high - for example for year 2020, there are only 9 dev months to September.  I need to prevent "future" dev months from showing values!