cancel
Showing results for
Did you mean:
Highlighted
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?

Any help is appreciated!

2 REPLIES 2
Highlighted
Community Support

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

Hi

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

Highlighted
Anonymous
Not applicable

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

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.

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021