Forum Discussion
Post PatronRunning total not working as expected
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.
Post PatronHi 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)
Super UserHi 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
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.