Forum Discussion
Post PatronRunning total not working as expected
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!
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.
6 Replies
- 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!
- MFelix
Super User
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
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.
