DAX: get sum of a value, x days before minimum user defined date

[Anonymous]

Hi all. 

I have the following scenario: 

1) Users selects a daterange (lets say, 7jan-8jan)

2) I want to dynamically extract the sum of another column, two days before the minimum filter value (5jan-6jan)

For me, the hard part is that the context "hides" the data i want to sum (5jan-6jan).

I have tried A LOT of stuff, with no succes. The following example is one of the (better) attempts. 

All help much appriciated!

two days before = 

VAR _mindate = calculate(MIN(testdata[sessiondate]),FILTER(testdata, testdata[amount]>0)

RETURN

CALCULATE(sum(testdata[amount]),
filter(ALLEXCEPT(testdata,'Date'[date]),testdata[sessiondate]=_mindate))

[AlB · Accepted solution]

Hi @Anonymous

It seems like you have a Date table already. If you're using the Date table in your slicer,  try this:

two days before =
VAR _mindate =
    MIN ( 'Date'[date] )
RETURN
    CALCULATE (
        SUM ( testdata[amount] ),
        FILTER (
            ALL ( 'Date' ),
            'Date'[date]=_mindate - 1 || 'Date'[date]=_mindate - 2
        )
    )

Code formatted with  

[Anonymous]

Hi @AlB

Works. Thank you so much! 

Yes, i have a date table that is connected to my main table. I guess my learning here, is not to forget that Power BI queries on the underlying relational structure. 

Your answer is definitely accepted, but ATM i can't accept as i'm getting a "authentication failed". I'll try again tomorrow.

- Thanks again!

[AlB]

@Anonymous

Cool. Yeah, quite a few features at the site have not been working properly over the last days

[AlB]

@Anonymous

Seems like the 'mark as solution' feature is working again