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.
My following measure works, however, I want to include a dynamic date range, so I don't have to update the measures every QT. What would be the best Dax to input to make this dynamic?
Solved! Go to Solution.
Hi @Anonymous ,
I think you might want to get the first day of the current quarter and the last day of the current quarter, you can try the following measures:
FirstQuaterDay = VAR TodaysDate = TODAY () VAR Calendar1 = CALENDAR ( DATE ( YEAR ( TodaysDate ), 1, 1 ), DATE ( YEAR ( TodaysDate ), 12, 31 ) ) VAR Calendar2 = ADDCOLUMNS ( Calendar1, "Quarter", ROUNDUP ( MONTH ( [Date] ) / 3, 0 ) ) VAR TodaysMonth = ROUNDUP ( MONTH ( TodaysDate ) / 3, 0 ) VAR Calendar3 = FILTER ( Calendar2, [Quarter] = TodaysMonth ) VAR FirstQUaterDay = MINX ( Calendar3, [Date] ) RETURN FirstQUaterDay
LastQuaterDay = VAR TodaysDate = TODAY () VAR Calendar1 = CALENDAR ( DATE ( YEAR ( TodaysDate ), 1, 1 ), DATE ( YEAR ( TodaysDate ), 12, 31 ) ) VAR Calendar2 = ADDCOLUMNS ( Calendar1, "Quarter", ROUNDUP ( MONTH ( [Date] ) / 3, 0 ) ) VAR TodaysMonth = ROUNDUP ( MONTH ( TodaysDate ) / 3, 0 ) VAR Calendar3 = FILTER ( Calendar2, [Quarter] = TodaysMonth ) VAR LastQuaterDay = MAXX ( Calendar3, [Date] )+1 RETURN LastQuaterDay
Results are as follows:
Adjust the measure as bellow:
Test Pipeline = CALCULATE ( SUM ( OpportunityFORREPORTING[opportunity.Net_New_Sales__c] ), OpportunityFORREPORTING[opportunity.CreatedDate] < [FirstQuaterDay], AND ( OpportunityFORREPORTING[opportunity.CloseDate] >= [FirstQuaterDay], OpportunityFORREPORTING[opportunity.CloseDate] <= [LastQuaterDay] ) )
For more details, you can refer to this post: First/Last Working Days
Here is a demo, please try it:
Best Regards,
Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
I think you might want to get the first day of the current quarter and the last day of the current quarter, you can try the following measures:
FirstQuaterDay = VAR TodaysDate = TODAY () VAR Calendar1 = CALENDAR ( DATE ( YEAR ( TodaysDate ), 1, 1 ), DATE ( YEAR ( TodaysDate ), 12, 31 ) ) VAR Calendar2 = ADDCOLUMNS ( Calendar1, "Quarter", ROUNDUP ( MONTH ( [Date] ) / 3, 0 ) ) VAR TodaysMonth = ROUNDUP ( MONTH ( TodaysDate ) / 3, 0 ) VAR Calendar3 = FILTER ( Calendar2, [Quarter] = TodaysMonth ) VAR FirstQUaterDay = MINX ( Calendar3, [Date] ) RETURN FirstQUaterDay
LastQuaterDay = VAR TodaysDate = TODAY () VAR Calendar1 = CALENDAR ( DATE ( YEAR ( TodaysDate ), 1, 1 ), DATE ( YEAR ( TodaysDate ), 12, 31 ) ) VAR Calendar2 = ADDCOLUMNS ( Calendar1, "Quarter", ROUNDUP ( MONTH ( [Date] ) / 3, 0 ) ) VAR TodaysMonth = ROUNDUP ( MONTH ( TodaysDate ) / 3, 0 ) VAR Calendar3 = FILTER ( Calendar2, [Quarter] = TodaysMonth ) VAR LastQuaterDay = MAXX ( Calendar3, [Date] )+1 RETURN LastQuaterDay
Results are as follows:
Adjust the measure as bellow:
Test Pipeline = CALCULATE ( SUM ( OpportunityFORREPORTING[opportunity.Net_New_Sales__c] ), OpportunityFORREPORTING[opportunity.CreatedDate] < [FirstQuaterDay], AND ( OpportunityFORREPORTING[opportunity.CloseDate] >= [FirstQuaterDay], OpportunityFORREPORTING[opportunity.CloseDate] <= [LastQuaterDay] ) )
For more details, you can refer to this post: First/Last Working Days
Here is a demo, please try it:
Best Regards,
Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Can this be used to filter the values on the report? I have a dynamic report but I want to filter the data for previous month.
I wrapped individually in filters and it worked. Thank you so much!
Thank you for your assistance. The last measure has an error message of "A function 'Calculate' that has been used un a True/False expression that is used as a table filter expression. This is not allowed."
DAX has a start of quarter function, you could try working out what that spits out in conjunction with TODAY() (adding three months as needed)?
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 |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |