Carry over Open Tickets from previous months to next month stack graph

[Anonymous]

I want to carry over all the open tickets from previous months to next month. For example, if I have 20 open tickets in JAN and 3 are closed I want to carry the remaining 17 tickets to FEB and if 3 more tickets that are opened in JAN are closed in the month of FEB then I want to carry over the remaining 14 tickets to MAR.

I want to visualize in the following format but I don't know how to do it(Please click on the link to check on the expected graph).

Stack Graph Visualization 

I don't know how to get it in the stacked format, can any please help me in resolving this query or letting me know if this can be done on Power BI.

Sharing the sample Data

Group	Closed Date	Number	Created Date	Priority	Sys Created Date	Stauts
Group1	5/24/2019 1:42	TIC00001	4/30/2019 23:05	3 - Moderate	4/30/2019 23:05	Closed
Group2	10/1/2019 13:10	TIC00002	4/25/2019 7:57	3 - Moderate	4/26/2019 10:13	Closed
Group1	7/1/2019 18:29	TIC00003	6/19/2019 6:27	3 - Moderate	6/19/2019 6:27	Closed
Group1	NULL	TIC00004	5/15/2019 15:51	3 - Moderate	5/15/2019 15:58	Open
Group3	7/16/2019 5:48	TIC00005	7/9/2019 4:18	2 - High	7/9/2019 4:18	Assigned
Group3	NULL	TIC00006	4/19/2019 16:51	3 - Moderate	4/19/2019 16:51	Open
Group3	7/22/2019 11:17	TIC00007	7/22/2019 11:10	3 - Moderate	7/22/2019 11:10	Closed
Group2	8/2/2019 12:27	TIC00008	5/22/2019 17:06	3 - Moderate	5/22/2019 17:33	Closed
Group5	5/24/2019 1:48	TIC00009	5/17/2019 10:09	4 - Low	5/17/2019 10:09	Closed
Group5	8/1/2019 7:08	TIC00010	7/9/2019 2:03	2 - High	7/9/2019 2:03	Closed
Group5	10/3/2019 21:37	TIC00011	4/18/2019 9:29	3 - Moderate	4/18/2019 9:58	Closed
Group1	NULL	TIC00012	2/26/2019 10:55	2 - High	2/26/2019 10:55	Assigned
Group3	6/18/2019 11:12	TIC00013	6/18/2019 10:56	3 - Moderate	6/18/2019 10:57	Closed
Group4	NULL	TIC00014	7/20/2019 5:45	2 - High	7/20/2019 5:45	Open
Group2	10/1/2019 13:16	TIC00015	6/23/2019 22:39	3 - Moderate	6/23/2019 22:46	Cancelled
Group2	NULL	TIC00016	4/24/2019 23:41	3 - Moderate	4/24/2019 23:50	Closed
Group2	5/16/2019 12:25	TIC00017	4/30/2019 18:47	2 - High	4/30/2019 18:47	Cancelled
Group4	6/10/2019 4:20	TIC00018	6/3/2019 9:34	3 - Moderate	6/3/2019 9:34	Cancelled
Group4	7/9/2019 3:03	TIC00019	5/29/2019 18:21	2 - High	5/29/2019 18:26	Closed
Group5	NULL	TIC00020	6/5/2019 18:35	3 - Moderate	6/5/2019 18:39	Open
Group4	NULL	TIC00021	2/5/2019 7:16	2 - High	2/5/2019 7:16	Open
Group2	NULL	TIC00022	5/10/2019 9:44	2 - High	5/10/2019 9:50	Open
Group3	NULL	TIC00023	4/22/2019 10:05	1 - Significant	4/22/2019 10:17	Open

[Anonymous · Accepted solution]

@Anonymous - This is an interesting challenge. Please see attached pbix. The main components of the solution:

1. Create 2 disconnected date tables.
2. Create the following measure, which refers to the 2 date tables:

Open Tickets End Of Month = 
var _CreateMonthStartDate = MIN('Created Date'[Date])
var _CreateMonthEndDate = MAX('Created Date'[Date])
var _AsOfMonthEnd = MAX('As Of Date'[Date])
return 
IF(
    _AsOfMonthEnd >=_CreateMonthEndDate,
    COUNTROWS(
        FILTER(
            'Ticket',
            Ticket[Created Date] >= _CreateMonthStartDate &&
            Ticket[Created Date] <= _CreateMonthEndDate &&
            OR(Ticket[Closed Date] > _AsOfMonthEnd, ISBLANK(Ticket[Closed Date]))
        )
    ),
    BLANK()
)

       3. Create a stacked bar chart. Note: The value in the chart corresponds with the size of the band. I don't know of a way to format it as you have in your example.

I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.

[Anonymous]

@Anonymous Awesome, Thank you it's working perfectly. I have two questions

1) How did you create the Date Table? I created using the DAX(Example: Date = CALENDAR(Date(2017,1,1),DATE(2020,12,31))) but I wanted to know how you created the Date table

2) The sorting of Months(As of Month)is not in order, I don't know why(let me know if you want the image) 

[Anonymous]

@Anonymous  -

1. The date table I use is a Power Query (M) script.

2. You can sort a column by another column.

[amitchandak]

Try something like this

Open till date = 
Var _min_date_for_open = CALCULATE(maxx(all('Date'[Date Filer]),'Date'[Date Filer]))
Var _Todays_date=CALCULATE(maxx(ALLSELECTED('Date'[Date Filer]),'Date'[Date Filer]))

Var _open_at_start CALCULATE(count(case[case ID]),case[case Date]<=_min_date_for_inventory)
Var   open_till_tody =CALCULATE(count(case[case ID]),filter(case,case[open Date]<=maxx(case,case[open Date].[Date])))
Var   close_till_tody =CALCULATE(count(case[case ID]),filter(case,case[close Date]<=maxx(case,case[close Date].[Date]))) 
//use userelation to change the join to close date
refer
https://community.powerbi.com/t5/Desktop/DATE-RANGE-SLICERS-TO-COMPARE-NETSALES-OF-A-DATE-RANGE/td-p/42251

return
_open_at_start+open_till_tody-close_till_tody

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601