Forum Discussion

Jtep's avatar
Jtep
Regular Visitor
9 years ago

Cummulative Total with filter retained

Hello,

I trying to get a cumulative total working for profit and loss, very similar to that found at http://www.daxpatterns.com/cumulative-total/

My formula:

=
CALCULATE (
[Total Net Profit and Loss],
FILTER (
ALL ( TradeLog_Table ),
TradeLog_Table[Close Date] <= MAX ( TradeLog_Table[Close Date] )
)
)

This works, but it returns more rows than desired, because my ‘TradeLog_table’ has been filtered via a lookup table and I want that filtering to be respected for the above calculation.  Ie the ‘FILTER(ALL(TradeLog_Table) part naturally returns all rows but I only want rows returned that have been filtered via the lookup table filter (in my case a slicer on the lookup table).  I’ve tried ALLSELECTED but this still returns all rows.

 

Thanks

Need Help

7 Replies

  • GilbertQ's avatar
    GilbertQ
    Super User
    9 years ago

    Hi Jtep

     

    What happens if you remove the ALL filter?

     

    My formula:
=
CALCULATE (
[Total Net Profit and Loss],
FILTER (TradeLog_Table ,
TradeLog_Table[Close Date] <= MAX ( TradeLog_Table[Close Date] )
)
)
    • Jtep's avatar
      Jtep
      Regular Visitor
      9 years ago

      Thanks for quick reply!  With ALL removed as per your code, unwanted rows are removed (good), but the profit and loss does not aggregate, ie is not cummulative.   What is returned is the same as my '[Total Net Profit and Loss]' measure, a simple measure of SUM(TradeLog_table[Profit and Loss]).  The aggregation is only working on one row, with the date expression appearing to have no effect.

      • GilbertQ's avatar
        GilbertQ
        Super User
        9 years ago

        Hi there


        Do you perhaps have a diagram of how the filtering is working. It appears as though the filtering is happening with the "Cross Filter Direction" set to both for another table. Which can always complicate things?