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
Power BI Monthly Update - September 2024
Check out the September 2024 Power BI update to learn about new features.
Microsoft Fabric & AI Learning Hackathon
Learn from experts, get hands-on experience, and win awesome prizes.
Fabric Community Update - September 2024
Find out what's new and trending in the Fabric Community.
