DAX help

[joe321]

I have a table with two year columns: Current_Year and Future_Year.

I need to SUM the amounts where Future_Year equals Current_Year, but exclude records where Future Year equals Current_Year.

Using the Data below, if Year 2000 is selected on the Current_Year slicer, I would need it to SUM rows 4 and 5 as the future year of 2000 equals the current year selected. Row 3 would be excluded because Current and future years equal.

CurYr-FutYr-Amount
1. 2000,2001,$1,000
2. 2000,2002,$2,000
3. 2000,2000 $3,000
4. 1999,2000,$4,000
5. 1998,2000,$5,000

 

[gazpage]

Finding it a little hard to understand exactly what you are after here, i.e. why this is a filter on Current Year and not filter on Future Year, presumably because of some other visualisations using the slicer.

I've written a formulation below that iterates through every combination of Current Year and Future Year and then includes or excludes depending on whether there are matches. There are probably ways this could be done using an EXCEPT, but that would require more thought.

Attempt = 
VAR 
    _Current = SELECTEDVALUE ( Data[Current] )
RETURN
    CALCULATE (
        SUM ( Data[Amount] ),
        FILTER (
            SUMMARIZE (
                ALL ( Data ),
                Data[Current],
                Data[Future]
            ),
            Data[Current] <> _Current
                && Data[Future] = _Current
        )
    )

 

[joe321]

Thanks for the reply. I still haven't been able to get your formula to work using the current year slicer. The formula below works if I use a future year slicer, but I really need everything to be driven off the current year slicer.

=CALCULATE([Total_Amount],FILTER(Data,NOT(CONTAINS(Data,Data[Current],Data[Future]))))

 

[gazpage]

Sorry to hear that. It worked fine for me. What problem are you having? Would you like me to send my pbix?