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.

Not applicable

Determine Start of Quarter In Date Table Based On MAX Date in Fact Table

I am trying to categorize the dates in my date table ('tCalendar') that fall into the current quarter so that I can use it as a filter in my visualizations. The current quarter is based on the latest [Date Completed] value in my fact table 'ReqMaster'. I created a column on the date table and attempted the first part of the formula to check for all dates equal-to or greater than the first of the quarter.


QTR Test = IF( 'tCalendar'[Date] >= CALCULATE( STARTOFQUARTER('ReqMaster Main'[Date Completed]), FILTER( ALL( 'ReqMaster Main'), 'ReqMaster Main'[Date Completed] = MAX('ReqMaster Main'[Date Completed]))), "Current Qtr", BLANK())

What is confusing to me is that the CALCULATE function is not returning the expected result - in this case, 7/1/2018. Instead it returns 7/2/2018. I'm assuming this is because my fact table does not have a value of 7/1/2018 in it, but does have 7/2/2018. But I would have expected the STARTOFQUARTER to have forced the value to be evaluated to be 7/1.  Can someone help me understand if there is a better way I should be doing this?




Any help is appreciated!

Community Support
Community Support

Hi rootdown42,


In your senario, you'd better input  date like  "7/1" manually as a condition in calculate function because STARTOFQUARTER will return the first date of the quarter in the current context for the specified column of dates.



Jimmy Tao

Not applicable

Thank you Jimmy, but this data will be updated going forward, so the goal is that no manual entry of date criteria will be needed. I want the current quarter to always be defined by the dates in the fact table. So I'm open to any solution that achieves this - it doesn't have to utilize STARTOFQUARTER.

Helpful resources

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