Hi, i am fairly new to DAX and Power BI and i am trying to calculate using DAX formula on the 30 days change of Count of Red and Yellow categories.
The first column is a calculated measure of current/today day count:
Count of CategoriesRed and Yellow =
CALCULATE(
COUNTA('[Age Categories]), '[Age Categories]
IN { "Greater than 90 less than 180 days", "Greater than 180 days" }
I would like to find out the count of tickets 30 days ago and calculate the % change.
Last 30 days opened = CALCULATE([Count of CategoriesRed and Yellow], KEEPFILTERS([opened_on] > NOW() - 30))
Last 30 days Closed = CALCULATE([Count of CategoriesRed and Yellow], KEEPFILTERS([Final Terminated Date] > NOW() - 30))
Cannot be that the 30 days ago Closed value is blank. Am i missing somthing here?
Change = CALCULATE(([Count of CategoriesRed and Yellow]-[Last 30 days opened]-[Last 30 days Closed]))
%Change = ([Count of CategoriesRed and Yellow]-[Change])/[Count of CategoriesRed and Yellow]
Not even sure if the DAX forumla or my mathematical makes sense.
Between these 30 days, there will be tickets opening and closing.
How can i capture this data accurately?
Can someone enlightened me? Thanks.
Here are some of the columns i have created as well:
Age = DATEDIFF([created_on],TODAY(),Day)
Group the age categories like "More than 30 days", "Between 10 to 30 days" and "Less than 30 days"
Sum only the Critical and High tickets and more than 10 days:
Count of Critical and High =
CALCULATE(
COUNTA('Table'[Age Categories]),
'Table'[Age Categories]
IN { "Between 10 to 30 days", "More than 30 days" }
As mentioned earlier, i need to find the count of tickets in the last 30 days.
Then the % change.
Thanks for your help again.
Helpful resources
FabCon Global Hackathon
Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!
FabCon Atlanta 2026
Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
