Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |