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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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
Super User

@Anonymous , Try a measure like

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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