Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to 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,
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
Thank you, that works wonders!
is it possible to set the same date slicer to a default value?
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.
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.
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))
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))
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))
That gives:
"The first argument to 'DATEADD' must specify a column."
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |