Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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!
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
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.
User | Count |
---|---|
120 | |
69 | |
68 | |
57 | |
50 |
User | Count |
---|---|
166 | |
82 | |
68 | |
65 | |
54 |