Power BI open tickets beginning of the month, including year, category and prioirty

[Anonymous]

Hello,

I am trying to calculate the open tickets at the beginning of the month for the whole history of a data set that I have (data is available since 2017), including a category and a priority. The data set would look as follows (as an example):

ID	creationDate	closureDate	closed	Priority	Category
12345	04-01-2019	06-04-2019	True	Low	Category A
23456	04-03-2019		False	High	Category B
34567	19-02-2019	20-02-2019	True	Medium	Category B
45678	21-02-2019	02-03-2019	True	Low	Category A

What I would like to get of out of this is (where primo is the number of tickets still open at the beginnen of the month):

Date	Open	Closed	Primo	Priority	Category
jan-19	1	0	0	Low	Category A
feb-19	1	0	1	Low	Category A
feb-19	1	1	0	Medium	Category B
mrt-19	1	0	0	High	Category B
mrt-19	0	1	2	Low	Category A
apr-19	0	0	1	High	Category B
apr-19	0	1	1	Low	Category A

So far I tried the following formula:

Open beginning month = 
SELECTCOLUMNS(
    ADDCOLUMNS(FILTER(DISTINCT(SELECTCOLUMNS(UNION(VALUES(Incidents[closureDate].[MonthNo]);VALUES(Incidents[creationDate].[MonthNo]));"Month";[closureDate].[MonthNo]));[Month]<>BLANK());
        "Open";COUNTAX(FILTER(ALL(Incidents);[creationDate].[MonthNo]=EARLIER([Month])||AND(Incidents[creationDate].[MonthNo]=EARLIER([Month]);Incidents[closureDate]>MAX(Incidents[creationDate])));[id]);
        "Close";COUNTAX(FILTER(ALL(Incidents);Incidents[closureDate].[MonthNo]=EARLIER([Month]));[id]);
        "Primo";COUNTAX(FILTER(ALL(Incidents);Incidents[creationDate].[MonthNo]<=EARLIER([Month])&&OR(Incidents[closureDate].[MonthNo]>=EARLIER([Month]);Incidents[closureDate]=BLANK()));[id]));
                "Month";FORMAT(DATE(2019;[Month];1);"MMMM");
                "Close";if([Close]>0;[Close];0);
                "Open";if([Open]>0;[Open];0);
                "Primo";if([Primo]>0;[Primo];0))

However, this calculates only the number of tickets at the beginning of the month over the last couple of years, instead of for example Jan 2019, Feb 2019, etc.

Is there anyone that can help me with creating the correct formula?

Thank you.

[Anonymous]

Hi crloef,

I am not 100% certain, but i think the result you are getting is based on purely month number, it does not condsider the year. I would suspect to achieve your desired result, you need to 'supply' the dates (first of the month) you are working with. Do you have a seperate Date table? if so, include a calculated column there, First of Month. With that in place (and a relationship set up) each of your creation and closure dates would have a First of Month relationship and so you could use that First of Month in your formula. 

[Anonymous]

Yes I have a seperate dates table, including only the first of the month. However, how would I put that in my formula then?