Filter measure dates relative to date slicer

[Anonymous]

I want to filter my measure dates based on a date slider that's on the same page.

I want a measure to get dates starting day before period that is selected with slicer.

For example if I have the following slider on the report page...

 ...the measure would get rows between dates 31.12.2016-18.7.2017; starting 1 day earlier than the slicer.

So if I create a table with the measure it would include rows starting from 31.12.2016 and ending to 18.7.2017

Any tips how I could achieve this?

[v-yuezhe-msft · Accepted solution]

@Anonymous,

Create measue using dax below.

Measure = VAR minDate = MIN('Final data'[Event Date])-1

VAR MaxDate = MAX('Final data'[Event Date])
RETURN
    CALCULATE(SUM('Final data'[Number of persons]),DATESBETWEEN('Final data'[Event Date],minDate,MaxDate))

Regards,
Lydia

[Anonymous]

Thank you, that works wonders!

[adild2k]

is it possible to set the same date slicer to a default value?

[Greg_Deckler]

If you date slicer is tied to a separate Calendar table, then you could always just get the MIN and MAX of that table and do a DATEADD, something like:

m_Measure = CALCULATE(SUM(Table[Column]),FILTER(Table,[Date]>=DATEADD(MIN(Calendar[Date]),-1,day) && Table[Date]<=MAX(Calendar[Date]))

Something like that. 

[Anonymous]

Thank you for your reply.

I have a separate calendar table and tried your method but it gives an error:

 A function 'MIN' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

[Greg_Deckler]

That's kind of strange. Try this:

m_Measure = VAR myMin = DATEADD(MIN(Calendar[Date]),-1,day)
VAR myMax = MAX(Calendar[Date]
RETURN
 CALCULATE(SUM(Table[Column]),FILTER(Table,[Date]>=myMin && Table[Date]<=myMax))

[Anonymous]

Giving the same error about the MIN on the first row.

I'm trying this with a simple dataset containing a 'Calendar' table which contains 'Date' and table 'Final data' which has two columns: 'Event Date' and 'Number or persons'.

Just to be sure I'm not messing something up here's the method:

m_Measure = VAR myMin = DATEADD(MIN(Calendar[Date]);-1;day)
VAR myMax = MAX(Calendar[Date])
RETURN
    CALCULATE(SUM('Final data'[Number of persons]);FILTER('Final data';[Event Date]>=myMin && 'Final data'[Event Date]<=myMax))

[Greg_Deckler]

That's a really weird error for that. Try this:

m_Measure = VAR myDate = MIN(Calendar[Date])
VAR myMin = DATEADD(myDate;-1;day)
VAR myMax = MAX(Calendar[Date])
RETURN
    CALCULATE(SUM('Final data'[Number of persons]);FILTER('Final data';[Event Date]>=myMin && 'Final data'[Event Date]<=myMax))

[Anonymous]

That gives:

"The first argument to 'DATEADD' must specify a column."