Forum Discussion
Running total / cumulative Sales
Hi Deva002,
Sorry for late response cause i just arrived hometown for new year ^_^
One of purposes when using external Dates table to allow you filter another fields in same fact table and the cummulative will be reflected. So if your fact table has only 2 columns Date and Value, i think you could use ALL method and Date column of that.
Cummulative. = CALCULATE(SUM(TableB[Value]),filter(ALL(TableB), TableB[Date]<=MAX(Dates[Date]) ))
In case you have additional fields and want to filter on that column, you need to handle with ALLEXCEPT, something like this:
Cummulative = CALCULATE(SUM(TableB[Value]),filter(ALLEXCEPT(TableB,TableB[AdditionalField/Dim]), TableB[Date]<=MAX(Dates[Date]) ))
So my recommendation is using Time Pattern as topic (external dates table):smileyvery-happy:
If this works for you please accept it as solution and also like to give KUDOS.
Best regards
Tri Nguyen
Memorable MemberHi Deva002,
There are some similar questions about cummulative with solution like:
With same approach: Create Dates table -> Create measure with filter All(Dates), Dates[Date] <= Max(Dates[Date])
Please let me know if you need more sample for your situation.
- tringuyenminh92
Hi Deva002,
Sorry for late response cause i just arrived hometown for new year ^_^
One of purposes when using external Dates table to allow you filter another fields in same fact table and the cummulative will be reflected. So if your fact table has only 2 columns Date and Value, i think you could use ALL method and Date column of that.
Cummulative. = CALCULATE(SUM(TableB[Value]),filter(ALL(TableB), TableB[Date]<=MAX(Dates[Date]) ))
In case you have additional fields and want to filter on that column, you need to handle with ALLEXCEPT, something like this:
Cummulative = CALCULATE(SUM(TableB[Value]),filter(ALLEXCEPT(TableB,TableB[AdditionalField/Dim]), TableB[Date]<=MAX(Dates[Date]) ))
So my recommendation is using Time Pattern as topic (external dates table):smileyvery-happy:
If this works for you please accept it as solution and also like to give KUDOS.
Best regards
Tri Nguyen