cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors