Cummulative sum over all periods keeping filters

[Anonymous]

Hi!

I've been trying to get a cummulative sum to work. I have a date field, "posting date" and an amount field, "amount". The table, "general ledger" can be filtered by some dimensions. I want to create a measure, "balance" that is the sum of "amount" for all entries up until the selected "posting date". For example if we have a table with month of posting date and "balance", the balance field should show the sum of all entires up until and including each month. I can get this to work, but not if I also want the user to be able to filter the visual or page on "posting date". For example to only show the last three months. I then want the table to show only three months and their balances, but still include all history when calculating the balances. How can this be done? 

Thanks,

Martin  

[smpa01]

@Anonymous  please provide sample data

[ValtteriN · Accepted solution]

Hi,

I think something like this should work:

RT = 
var maxdate = MAX('Calendar'[Date]) return

CALCULATE(SUM('General Lefger'[Linesum]),
'Calendar'[Date]<= maxdate,
ALL('Calendar'[Date]))

The Key here is to use ALL to remove filter context even if you have e.g. December selected.

Additionally, I recommend checking this article by SQLBI when it comes to running totals: https://www.sqlbi.com/articles/computing-running-totals-in-dax/

I hope this helps and if it does consider accepting this as a solution and giving the post a thumbs up!

[Anonymous]

This solved it. Or my actual issue was that I did not use a date table but a date column in the fact table. So that was my issue but this is the way of doing it.