cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
frank2987043
Regular Visitor

Filter where date column between 2 relative dates

I have a measure that needs to meet the following conditions:

  • Sums the amount of hours for a task, where the starttime of the task is between
    • The the first date of the previous quarter
    • The last date of 3 quarters into the future

I'm so confused by the fact that DATEADD requires a vector format instead of a scalar, so I can't use the TODAY function.

 

The closest I've gotten is

Measure1 = CALCULATE(SUM(Tasks[hours]) / 60, DATESBETWEEN(Tasks[Startdate], FIRSTDATE(DATEADD(STARTOFQUARTER('Date'[Date]), -1, QUARTER)), LASTDATE(DATEADD(ENDOFQUARTER('Date'[Date]), 3,QUARTER))))
 
In this Measure the date table is a custom range of subsequent days similar to many other such tables found on this forum.
 
I hope someone can help me find a solution. Which might also include filters or slicers. I haven't gotten those to work because of my need for quarters, which aren't present in filters.
1 REPLY 1
amitchandak
Super User IV
Super User IV

@frank2987043 , Try a measure like

Rolling 4 = CALCULATE(SUM(Tasks[hours]) / 60,DATESINPERIOD('Date'[Date],maxx('DAte',STARTOFQUARTER( DATEADD('Date'[Date],-1,QUARTER)),4,QUARTER)) )



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors