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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
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?

 

Untitled.jpg

 

Any help is appreciated!

2 REPLIES 2
v-yuta-msft
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.

 

Regards,

Jimmy Tao

Anonymous
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

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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