Calculate 30 day change for open and closed tickets

[lavlyn78]

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.

[amitchandak]

@lavlyn78,

if you opened and active, not closed in last 30day, you can have measure like

Active Last 30 days opened = CALCULATE([Count of CategoriesRed and Yellow], [opened_on] > (NOW() - 30) && Final Terminated Date] > NOW() )

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[lavlyn78]

Thank you for your reply. I have added your formula but it still gives me a blank value. 

I have a sample pbix file, how to i attach it here? I have attached the table instead 

IndexstatusPrioritycreated_onFinal Terminated DateState

1	R	Critical	17/5/2024	24/5/2024	Closed
2	R	Critical	29/4/2024	1/5/2024	Closed
3	R	Critical	29/4/2024	1/5/2024	Closed
4	R	Critical	26/4/2024	28/5/2024	Closed
5	R	Critical	29/4/2024	1/5/2024	Closed
6	R	Critical	29/4/2024	1/5/2024	Closed
7	R	Critical	2/5/2024	2/5/2024	Closed
8	V	Medium	1/5/2024	1/5/2024	Closed
9	R	Critical	8/5/2024	14/5/2024	Closed
10	R	Critical	21/4/2024	13/5/2024	Closed
11	R	Critical	16/5/2024	21/5/2024	Closed
12	V	Medium	17/5/2024	22/5/2024	Closed
13	R	Critical	29/4/2024	1/5/2024	Closed
14	R	Critical	8/5/2024	9/5/2024	Closed
15	R	Critical	14/4/2024	16/4/2024	Closed
16	V	Medium	15/4/2024	13/5/2024	Closed
17	R	Critical	24/5/2024	27/5/2024	Closed
18	R	Critical	19/4/2024	26/4/2024	Closed
19	V	Medium	11/5/2024	13/5/2024	Closed
20	R	Critical	2/5/2024	6/5/2024	Closed
21	R	Critical	21/5/2024	23/5/2024	Closed
22	R	Critical	7/5/2024	28/5/2024	Closed
23	R	Critical	3/5/2024	8/5/2024	Closed
24	R	Critical	29/4/2024	1/5/2024	Closed
25	R	Critical	25/4/2024	26/4/2024	Closed
26	R	Critical	29/4/2024	1/5/2024	Closed
27	R	Critical	20/4/2024	28/5/2024	Closed
28	R	Critical	24/5/2024	28/5/2024	Closed
29	V	Medium	23/5/2024	26/5/2024	Closed
30	V	Medium	22/5/2024	24/5/2024	Closed
31	R	Critical	29/4/2024	1/5/2024	Closed
32	R	Critical	2/5/2024	27/5/2024	Closed
33	R	Critical	17/5/2024	17/5/2024	Closed
34	R	Critical	20/4/2024	28/5/2024	Closed
35	R	Critical	20/4/2024	28/5/2024	Closed
36	R	Critical	25/4/2024	28/5/2024	Closed
37	R	Critical	25/4/2024	28/5/2024	Closed
38	R	Critical	28/5/2024	28/5/2024	Closed
39	R	Critical	28/5/2024	28/5/2024	Closed
40	R	Critical	17/4/2024	22/4/2024	Closed
41	M	Medium	2/5/2024	28/5/2024	Closed
42	R	Critical	17/5/2024	21/5/2024	Closed
43	R	Critical	21/5/2024	21/5/2024	Closed
44	R	Critical	29/4/2024	1/5/2024	Closed
45	R	Critical	29/4/2024	1/5/2024	Closed
46	N	Medium	13/5/2024	null	Open
47	A	High	30/4/2024	null	Open
48	N	Medium	15/4/2024	null	Open
49	N	Medium	10/5/2024	null	Open
50	A	High	17/4/2024	null	Open
51	A	High	22/5/2024	null	Open
52	A	High	9/5/2024	null	Open
53	A	High	22/5/2024	null	Open
54	A	High	17/4/2024	null	Open
55	N	Medium	13/5/2024	null	Open
56	A	High	17/5/2024	null	Open
57	N	Medium	22/5/2024	null	Open
58	N	Medium	15/4/2024	null	Open
59	O	High	30/4/2024	null	Open
60	N	Medium	28/5/2024	null	Open
61	A	High	30/4/2024	null	Open
62	N	Medium	28/5/2024	null	Open
63	N	Medium	28/5/2024	null	Open
64	N	Medium	28/5/2024	null	Open
65	N	Medium	28/5/2024	null	Open
66	N	Medium	28/5/2024	null	Open
67	N	Medium	28/5/2024	null	Open
68	N	Medium	28/5/2024	null	Open
69	N	Medium	28/5/2024	null	Open
70	N	Medium	28/5/2024	null	Open
71	N	Medium	28/5/2024	null	Open
72	N	Medium	28/5/2024	null	Open
73	N	Medium	28/5/2024	null	Open
74	N	Medium	28/5/2024	null	Open
75	N	Medium	28/5/2024	null	Open
76	N	Medium	28/5/2024	null	Open
77	N	Medium	28/5/2024	null	Open
78	N	Medium	28/5/2024	null	Open
79	N	Medium	28/5/2024	null	Open
80	N	Medium	28/5/2024	null	Open
81	N	Medium	28/5/2024	null	Open
82	A	High	17/4/2024	null	Open
83	N	Medium	15/4/2024	null	Open
84	N	Medium	15/4/2024	null	Open
85	A	High	30/4/2024	null	Open
86	A	High	24/5/2024	null	Open
87	N	Medium	28/5/2024	null	Open
88	N	Medium	27/5/2024	null	Open
89	A	High	30/4/2024	null	Open
90	N	Medium	24/5/2024	null	Open
91	N	Medium	26/4/2024	null	Open
92	A	High	22/5/2024	null	Open
93	A	High	22/5/2024	null	Open
94	N	Medium	22/5/2024	null	Open
95	A	High	16/4/2024	null	Open
96	N	Medium	13/5/2024	null	Open
97	N	Medium	22/5/2024	null	Open
98	A	High	22/5/2024	null	Open
99	H	Medium	1/5/2024	null	Open
100	P	Medium	23/5/2024	null	Open
101	A	High	23/5/2024	null	Open
102	A	High	22/5/2024	null	Open
103	N	Medium	8/5/2024	null	Open
104	A	High	22/5/2024	null	Open
105	N	Medium	22/5/2024	null	Open
106	A	High	22/5/2024	null	Open
107	A	High	22/5/2024	null	Open
108	A	High	22/5/2024	null	Open
109	A	High	22/5/2024	null	Open
110	A	High	22/5/2024	null	Open
111	N	Medium	22/5/2024	null	Open
112	A	High	22/5/2024	null	Open
113	N	Medium	22/5/2024	null	Open
114	A	High	22/5/2024	null	Open
115	A	High	22/5/2024	null	Open
116	A	High	22/5/2024	null	Open
117	N	Medium	22/5/2024	null	Open
118	N	Medium	22/5/2024	null	Open
119	N	Medium	22/5/2024	null	Open
120	A	High	22/5/2024	null	Open
121	N	Medium	22/5/2024	null	Open
122	A	High	22/5/2024	null	Open
123	A	High	22/5/2024	null	Open
124	N	Medium	13/5/2024	null	Open
125	A	High	22/5/2024	null	Open
126	A	High	14/5/2024	null	Open
127	N	Medium	28/5/2024	null	Open
128	H	Medium	1/5/2024	null	Open
129	A	High	4/5/2024	null	Open
130	N	Medium	22/5/2024	null	Open
131	A	High	22/5/2024	null	Open
132	A	High	22/5/2024	null	Open
133	A	High	22/5/2024	null	Open
134	A	High	22/5/2024	null	Open
135	N	Medium	27/4/2024	null	Open
136	A	High	16/4/2024	null	Open
137	N	Medium	22/5/2024	null	Open
138	A	High	22/5/2024	null	Open
139	N	Medium	22/5/2024	null	Open
140	N	Medium	22/5/2024	null	Open
141	A	High	22/5/2024	null	Open
142	P	Medium	23/5/2024	null	Open
143	N	Medium	22/5/2024	null	Open
144	A	High	22/5/2024	null	Open
145	A	High	22/5/2024	null	Open
146	N	Medium	22/5/2024	null	Open
147	A	High	24/5/2024	null	Open
148	A	High	22/5/2024	null	Open
149	A	High	17/4/2024	null	Open
150	A	High	22/5/2024	null	Open
151	N	Medium	22/5/2024	null	Open
152	N	Medium	22/5/2024	null	Open
153	A	High	22/5/2024	null	Open
154	A	High	22/5/2024	null	Open
155	N	Medium	11/5/2024	null	Open
156	N	Medium	22/5/2024	null	Open
157	A	High	22/5/2024	null	Open
158	N	Medium	28/5/2024	null	Open

[lavlyn78]

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.