SUM for Filtered Values

[DMeisner]

I'm having a problem with something that seems really simple but I've wasted nearly a day trying to get it together. I suspect my TSQL background is interfering with may DAX thinking.

I have two tables of data; one of documents each having a date and an initial amount (i.e. invoice, payment, credit or debit memo, etc.) and the other contains adjustments to the documents initial amount with an application date. There is a 1-many (document[doc_id]->adjustment[doc_id]) relationship.

So I added document table visualization (including the initial amount). I then setup a date table with a 1-many (date[date]>-document[date]) relationship so could add a date slicer that filters the documents where the date is on or before the slicer date. Looks good so far.

I'd like to add a new column for the document balance as of the slicer date. This would be the initial amount less adjustments where the application date is the same or prior to the slicer.

I added an adjustment table visualization. However it showed data based on whats in the document table visualization (no filtering on the application date). This made sense to me, so I tried to add a date[date]>->adjustment[doc_id] relationship. This failed until made the document[doc_id]->adjustment[doc_id] 'inactive'. So now I'm seeing all adjustments applied on or before the slider date.

First things first, I figured I'd add a column that shows the sum of adjustments for the document from on or before the slicer date. After several failures on this front, I came up with:
Adjustment Amount = CALCULATE(SUM(ARAdjustments[Adjustment Amount]),USERELATIONSHIP(ARDoc[ARDocKey],ARAdjustments[ARDocKey]))

Ahh! Looks good until I moved the slicer to a date prior to the application date. Adjustment Amount does not change! It seems I've lost the filter from [date]>-document[doc_id].

Please help!

[Anonymous]

@DMeisner 

Tables and their calculated columns are loaded during refresh time and are not affected by filters, slicers, etc. Sounds like you are want to create a measure which is most definitely affected by slicers and such. 

[DMeisner]

Ok - changed from a calculated column to a measure. Doesn't work. Appears ok when the date slicer is set to include all the adjustments. But when I set the date slicer prior to the application date and after the document dates - the measure still seems to suml the adjustments, when they should be filtered out.

[DMeisner · Accepted solution]

To whom it may concern:

Here's the solution that worked:
I created two measures...a hidden one:

_adjustments = IF(AND(HASONEVALUE(ARDoc[ARDocKey]), SUM(ARDoc[Initial Balance]) <> 0),

CALCULATE(SUM('ARAdjustments'[Adjustment Amount]), USERELATIONSHIP(ARAdjustments[Date Applied], 'Date'[Date]))
// I believe the different USERELATIONSHIP() parms fixed the slicer issue.
// The HASONEVALUE() keeps it from returning a total row and the SUM(ARDoc[Initial Balance]) <> 0 causes some invalid data from messing up the rows.

// Also note that rows that do not meet these criteria return <BLANK>, NOT 0 (zero).
...and the measure I ultimately put in the table visual:

Adjustments = SUMX(VALUES(ARDoc[ARDocKey]), [_adjustments])

This corrected the slicer behavior and gives my report the correct column total. 🙂