Essentially I am using two linked tables with PowerPivot. First one contains following columns [Date], [Amount], [Id]. The second table only contains the id's [Id] that are used to filter the first table. This has worked great until I combined this with a measure that calculates a cumulative sum.
=CALCULATE(SUM( [Amount] ) ; FILTER(ALL( 'Table') ; 'Table'[Date] <= MAX('Table'[Date] )))
I have tried to apply various filters directly into the Calculate-statement, but none of these seem to work. Conditions that should be correct always seem to return an error that the columns can't be determined in the current context. One thing to note is that the [Id] column is a string value.
Optimal solution would be a method for calculating the cumulative value with any Id filters applied to the PivotTable (I use several of these tables to filter to one table with the data), but having multiple variations of the calculated measure is fine too.
I'm new to DAX, so I highly appreciate all the with this!
I have had a similar problem. One workaround is to create a new table (i've called it 'Dummy') with just the relevant dates, and created a calculated column in 'Dummy' with the following:
=SUMX( FILTER('Table','Table'[Date]<='Dummy'[Date]) , 'Table'[Amount] )
After this you should be able to relate 'Table' and 'Dummy' and just pull the cumulative figure from 'Dummy' as you need it.
It's not ideal, and if anyone has a neat formula for obtaining this result without resorting to the creation of an extra table shout.