Cummulative Total with filter retained

[Jtep]

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

[GilbertQ]

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]

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]

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?

[Jtep]

Diagram attached.  A normal one to many relationship established.  Filtering is established by a slicer connected to my lookup table (table at the top).  This selects a particular broker, then the rows on the connected table below are filtered to show only rows with the selected broker(s).  Essentially I want a function like ALL that returns all 'currently filtered rows' opposed to 'all' rows'.

[GilbertQ]

Hi @Jtep

I would think that if you clicked on the Broker that it would then Filter the data to the Broker selected and then still show the cumulative total?

[Jtep]

If I attach the slicer to my data table the cummulative total works, but I need filtering controlled by the lookup table for other measures. 

I'm gonna have a break.  Cheers for the help.

[GilbertQ]

So if you create a slicer on the data table it works?

But if you create a slicer off the TradeLog_Table it does not work?

Does the TradeLog_Table only have one entry per Broker?