Measure that calculates the difference between calls and adds this difference in the next month

[hugo_barbara]

Hey, guys! Can you help me? I need to create a measure that calculates the difference between tickets opened and closed within a month and this difference is added to the tickets opened in the following month. When this difference is negative or zero, it will not be carried over to the next month.

Example:

I tried to create some measurements but without success, follow my line of reasoning:

DifferenceCallsAccumulated =
VAR CurrentDate = MAX(dCalendario[Date])
VAR NextMonth = EOMONTH(CurrentDate, 1)

// Calculate the difference between opened and closed calls for the current month
VAR CurrentMonthDifference =
CALCULATE(
[Quantity of Task Created] - [Quantity of Tasks Closed],
YEAR(fto_incidents_ptk[Opened On - Calendar]) = YEAR(CurrentDate) &&
MONTH(fto_incidents_ptk[Opened On - Calendar]) = MONTH(CurrentDate)
)

// Calculate the difference between opened and closed calls for the next month
VAR NextMonthDifference =
CALCULATE(
[Quantity of Task Created],
YEAR(fto_incidents_ptk[Opened On - Calendar]) = YEAR(NextMonth) &&
MONTH(fto_incidents_ptk[Opened On - Calendar]) = MONTH(NextMonth)
) -
CALCULATE(
[Quantity of Tasks Closed],
YEAR(fto_incidents_ptk[Closed On - Calendar]) = YEAR(NextMonth) &&
MONTH(fto_incidents_ptk[Closed On - Calendar]) = MONTH(NextMonth)
)

RETURN
IF(
NextMonthDifference > 0,
CurrentMonthDifference + NextMonthDifference,
CurrentMonthDifference
)

[talespin · Accepted solution]

hi @hugo_barbara 

Please check if this is what you want, I hope you understand what you are doing. Your difference is now incorrect.

The Logic used for New Tickets and Closed Tickets

Open Ticket = Ticket with Open date in selected month is counted as open, irrespective of its status.

Closed Ticket = Ticket with a closed date in selected month.

STEP1 : Create this data model, I have one date table linked to your table on both date columns, keep CreateDate as active and ClosedDate as inactive and both relationship as Single, one(CALENDAR) to many(Data Table). Please change column and table names accordingly.

STEP2 Create these four measures.

--------------------------------------------------------------------------------------

Closed Tickets = CALCULATE( COUNT(Tickets[Task]), USERELATIONSHIP('CALENDAR'[Date], Tickets[Closed At]) )

-------------------------------------------------------------------------------------- 

Difference = [NewTickets] - [Closed Tickets]

-------------------------------------------------------------------------------------- 

NewTickets = CALCULATE( COUNT(Tickets[Task]) )

 --------------------------------------------------------------------------------------

Modify New Tickets =

VAR _Year = SELECTEDVALUE('CALENDAR'[YEAR])

VAR _mth = SELECTEDVALUE('CALENDAR'[MonthNo])

VAR _dt = EDATE(DATE(_Year, _mth, 1), -1)

VAR _prevValYear = YEAR(_dt)

VAR _prevValMth = MONTH(_dt)

VAR _prevMthNewTickets = CALCULATE( COUNT(Tickets[Task]), REMOVEFILTERS('CALENDAR'), 'CALENDAR'[YEAR] = _prevValYear, 'CALENDAR'[MonthNo] = _prevValMth )

VAR _prevMthClosedTickets = CALCULATE( COUNT(Tickets[Task]), REMOVEFILTERS('CALENDAR'), USERELATIONSHIP('CALENDAR'[Date], Tickets[Closed At]), 'CALENDAR'[YEAR] = _prevValYear && 'CALENDAR'[MonthNo] = _prevValMth )

VAR currMthNewTickets = CALCULATE( COUNT(Tickets[Task]) )

VAR _Diff = (_prevMthNewTickets - _prevMthClosedTickets)

RETURN currMthNewTickets + IF(_Diff > 0, _Diff, BLANK())

 --------------------------------------------------------------------------------------

STEP3 Now as you see in screenshot, please these measures in same order. Change the Column color to hide the correct open ticket count column(Change color to white or whatever matches your background).

[hugo_barbara]

@talespin Thank you very much!! It worked out!!!!

[talespin]

hi @hugo_barbara 

You're welcome.

[talespin]

hi @hugo_barbara 

Can you please share sample data or pbix file with all sensitive data removed.

[hugo_barbara]

hi @talespin here is an example of the data I use when I need help..

tks

Created At	Closed At	Task	State
19/12/2023 09:35		task0510560	Backlog
25/01/2024 15:27		task0522824	Backlog
08/01/2024 10:51		task0515841	Backlog
04/01/2024 12:09		task0514727	Backlog
11/01/2024 17:55		task0517696	Backlog
22/01/2024 09:53		task0521663	Backlog
22/01/2024 16:09		task0521777	Backlog
22/01/2024 16:27		task0521786	Backlog
22/01/2024 17:52		task0521802	Backlog
25/01/2024 14:29		task0522808	Backlog
29/01/2024 15:52		task0524232	Backlog
31/01/2024 11:26		task0524979	Backlog
02/02/2024 11:28		task0525766	Backlog
07/02/2024 15:50		task0528089	Backlog
08/02/2024 11:44		task0528436	Backlog
08/02/2024 14:57		task0528524	Backlog
08/02/2024 17:25		task0528597	Backlog
31/01/2024 15:10		task0525048	Backlog
01/02/2024 17:07		task0525523	Backlog
14/02/2024 16:11		task0530067	Backlog
09/01/2024 19:20		task0516798	Backlog
07/02/2024 15:28		task0528080	Backlog
13/02/2024 13:46		task0529738	Backlog
14/02/2024 16:31		task0530076	Backlog
14/02/2024 17:21		task0530088	Backlog
04/12/2023 09:37		task0505040	Backlog
09/01/2024 15:59		task0516721	Backlog
10/01/2024 07:46		task0516908	Backlog
17/01/2024 15:41		task0520344	Backlog
01/02/2024 17:38		task0525532	Backlog
06/02/2024 12:23		task0527424	Backlog
01/11/2023 15:05	01/11/2023	task0490702	Canceled
05/02/2024 13:53	05/02/2024	task0526892	Canceled
14/02/2024 17:26	14/02/2024	task0530091	Canceled
05/02/2024 14:00	05/02/2024	task0526897	Canceled
28/09/2023 11:31	28/09/2023	task0478953	Canceled
24/03/2023 14:47	31/03/2023	task0410886	Closed
28/03/2023 11:29	06/04/2023	task0411778	Closed
10/04/2023 17:24	18/05/2023	task0415738	Closed
13/04/2023 09:54	08/05/2023	task0416944	Closed
13/04/2023 09:57	08/05/2023	task0416945	Closed
13/04/2023 17:09	21/08/2023	task0417098	Closed
18/04/2023 11:18	18/05/2023	task0418528	Closed
24/04/2023 15:26	27/07/2023	task0424056	Closed
25/04/2023 11:58	29/05/2023	task0424712	Closed
26/04/2023 18:51	23/05/2023	task0426680	Closed
26/04/2023 19:12	18/05/2023	task0426691	Closed
27/04/2023 15:07	05/05/2023	task0426975	Closed
27/04/2023 17:41	05/05/2023	task0427041	Closed
28/04/2023 09:38	05/05/2023	task0427355	Closed
28/04/2023 10:01	11/09/2023	task0427363	Closed
28/04/2023 11:22	05/05/2023	task0427420	Closed
07/05/2023 21:16	29/05/2023	task0429784	Closed
08/05/2023 17:23	15/05/2023	task0430104	Closed
10/05/2023 17:21	19/05/2023	task0430831	Closed
15/05/2023 18:48	14/08/2023	task0432248	Closed
17/05/2023 20:29	27/07/2023	task0433056	Closed
18/05/2023 19:00	20/06/2023	task0433609	Closed
19/05/2023 11:10	17/08/2023	task0434283	Closed
22/05/2023 11:03	15/08/2023	task0436603	Closed
23/05/2023 11:59	29/05/2023	task0437197	Closed
01/06/2023 15:47	17/08/2023	task0440733	Closed
01/06/2023 18:24	14/07/2023	task0440772	Closed
06/06/2023 15:23	14/07/2023	task0442217	Closed
07/06/2023 14:30	12/09/2023	task0442540	Closed
22/06/2023 11:13	19/07/2023	task0447210	Closed
03/07/2023 17:24	21/07/2023	task0450441	Closed
05/07/2023 12:48	12/07/2023	task0450993	Closed
06/07/2023 18:38	19/07/2023	task0451556	Closed
13/07/2023 17:45	19/07/2023	task0454120	Closed
09/08/2023 18:35	17/08/2023	task0463329	Closed
11/08/2023 15:36	15/08/2023	task0464197	Closed
21/08/2023 16:37	11/09/2023	task0467658	Closed
22/08/2023 13:53	30/01/2024	task0467908	Closed
22/08/2023 15:25	16/11/2023	task0467941	Closed
04/10/2023 17:01	22/11/2023	task0482022	Closed
06/10/2023 16:57	07/12/2023	task0482856	Closed
09/10/2023 10:59	30/10/2023	task0483514	Closed
09/10/2023 16:48	22/11/2023	task0483651	Closed
17/10/2023 13:38	24/10/2023	task0485481	Closed
23/10/2023 10:12	24/11/2023	task0486772	Closed
25/10/2023 09:55	01/11/2023	task0487602	Closed
01/11/2023 17:50	24/11/2023	task0490802	Closed
06/11/2023 17:37	21/11/2023	task0492658	Closed
14/11/2023 14:35	21/11/2023	task0495646	Closed
14/11/2023 14:42	21/11/2023	task0495652	Closed
14/11/2023 15:02	19/12/2023	task0495668	Closed
14/11/2023 15:10	21/11/2023	task0495673	Closed
16/11/2023 15:10	07/12/2023	task0496318	Closed
16/11/2023 15:32	20/12/2023	task0496341	Closed
21/11/2023 16:24	07/12/2023	task0498564	Closed
24/11/2023 15:07	08/12/2023	task0501294	Closed
27/11/2023 18:31	07/12/2023	task0502321	Closed
27/11/2023 18:42	14/12/2023	task0502325	Closed
28/11/2023 19:21	19/12/2023	task0502785	Closed
01/12/2023 12:04	07/12/2023	task0504046	Closed
05/12/2023 11:02	11/12/2023	task0505683	Closed
05/12/2023 16:19	31/01/2024	task0505811	Closed
22/01/2024 14:05	08/02/2024	task0521729	Closed
25/01/2024 09:18	08/02/2024	task0522644	Closed
29/01/2024 15:16	01/02/2024	task0524215	Closed
31/01/2024 15:50	08/02/2024	task0525071	Closed
16/03/2023 10:16	27/03/2023	task0408468	Closed
23/03/2023 10:33	25/04/2023	task0410456	Closed
24/03/2023 12:54	17/08/2023	task0410850	Closed
28/03/2023 10:16	25/04/2023	task0411749	Closed
04/04/2023 14:42	08/05/2023	task0414032	Closed
17/04/2023 10:49	08/05/2023	task0418093	Closed
19/12/2023 13:40	05/02/2024	task0510618	Closed
21/03/2023 19:18	06/04/2023	task0409940	Closed
07/05/2023 15:55	21/08/2023	task0429760	Closed
17/07/2023 14:02	14/08/2023	task0455159	Closed
24/07/2023 14:08	17/08/2023	task0457783	Closed
26/07/2023 13:56	15/08/2023	task0458456	Closed
28/07/2023 08:52	31/08/2023	task0458994	Closed
02/08/2023 14:52	11/08/2023	task0460745	Closed
02/08/2023 15:04	22/08/2023	task0460749	Closed
02/08/2023 17:55	18/08/2023	task0460845	Closed
03/08/2023 14:54	23/08/2023	task0461169	Closed
04/08/2023 17:27	18/08/2023	task0461606	Closed
11/10/2023 10:13	31/10/2023	task0484206	Closed
27/03/2023 17:53	06/04/2023	task0411613	Closed
22/05/2023 15:18	22/05/2023	task0436713	Closed
25/05/2023 19:43	29/05/2023	task0438089	Closed
07/07/2023 10:50	07/07/2023	task0451762	Closed
11/10/2023 13:55	11/10/2023	task0484279	Closed
20/03/2023 11:57	27/03/2023	task0409434	Closed
22/03/2023 13:34	25/04/2023	task0410173	Closed
14/04/2023 14:37	29/05/2023	task0417340	Closed
26/04/2023 19:19	02/05/2023	task0426694	Closed
03/05/2023 18:26	13/07/2023	task0428792	Closed
07/05/2023 12:55	12/07/2023	task0429749	Closed
08/05/2023 10:19	13/06/2023	task0429955	Closed
08/05/2023 17:02	12/05/2023	task0430099	Closed
25/05/2023 19:31	29/05/2023	task0438085	Closed
01/06/2023 15:23	19/06/2023	task0440726	Closed
12/06/2023 18:33	13/06/2023	task0444183	Closed
13/06/2023 14:27	11/07/2023	task0444432	Closed
19/06/2023 16:38	07/07/2023	task0446195	Closed
06/07/2023 10:29	07/07/2023	task0451343	Closed
14/07/2023 14:19	17/07/2023	task0454400	Closed
14/07/2023 18:53	28/09/2023	task0454532	Closed
21/07/2023 11:20	15/08/2023	task0456897	Closed
27/07/2023 08:48	21/08/2023	task0458666	Closed
27/07/2023 10:14	24/08/2023	task0458681	Closed
31/07/2023 10:56	23/08/2023	task0459733	Closed
01/08/2023 12:09	11/09/2023	task0460247	Closed
01/08/2023 15:19	18/08/2023	task0460314	Closed
09/08/2023 09:20	11/08/2023	task0463061	Closed