Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Filter measure dates relative to date slicer

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...

PBIDesktop_2017-12-19_14-14-04.png

 

 ...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?

1 ACCEPTED SOLUTION
v-yuezhe-msft
Employee
Employee

@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))

1.JPG2.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

9 REPLIES 9
v-yuezhe-msft
Employee
Employee

@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))

1.JPG2.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Thank you, that works wonders!

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

Greg_Deckler
Super User
Super User

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. 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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))

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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))

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

That gives:

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.