There are some well known design patterns and blogs for handling running sum commonly used on reports such as Profit and Loss Statements.
https://www.daxpatterns.com/cumulative-total/
I’ll show here an alternative approach that I commonly sue when we have a Many to Many table such as:
– A table mapping account codes to Financial Reporting Ranges
– A Date Range table to show YTD, WTD without any DAX
If we take the first example we may have a Profit and Loss Report with four lines on it as below with line 4 being a running sum (Net Invoice Sales)
The Rules table that stores mappings from account codes to the reporting ranges and headings may looks something like below for the first three lines of report
If we add Line #4 as below we now have a “running sum” without the need for any DAX, the many to many join will do the same function as a running sum.
Your mileage may vary and there is nothing wrong with the DAX pattern,
but for some scenarios we may have a M2M table anyway, so we can also leverage it for supporting custom aggregates like running sums..
