Count Create and End Dates in columns

[Anonymous]

Hello all!

I'm struggling trying to do the following calculus and I need your help. I need to sum the Creation_date and End_date of an IT ticket table. The goal is to create a new table with the date and two columns with the sum of these dates within the ticket table. The idea is to show on a graph this information, as with the original table I have to filter from OpenDate or CloseDate and that would be incorrect.

So first of all, I created a new table called Dates with the following DAX formula:

Dates = CALENDARAUTO()

After that, I created a couple of columns in order to sum the created and end dates.
 
OpenCount = COUNTROWS(FILTER(EV_Tickets; EV_Tickets[CREATION_DATE_UT].[Date] IN DATESMTD(Dates[Date])))
CloseCount = COUNTROWS(FILTER(EV_Tickets; EV_Tickets[END_DATE_UT].[Date] IN DATESMTD(Dates[Date])))
 
EV_tickets is the table which contains the info I need.

 In this case, the formula countrows is doing an accumulative sum of the previous days and it resets again when it changes to other month.
 
Date             OpenCount    CloseCount
01/11/2017    2    
02/11/2017    327               300
03/11/2017    566               528
04/11/2017    567               528
05/11/2017    573               531

I need a syntax which counts the open and closed tickets for each day, not accumulating like this case. I tried with different formulas such us count etc, but it didn't worked.

Could you please help?

Thanks a lot!!!! 🙂

[v-cherch-msft]

Hi @Anonymous

It seems you may use  USERELATIONSHIP Fuction or GENERATE Function.Below are some articles for your reference.If you need further help, please share some data sample and expected output. You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364

https://www.sqlbi.com/articles/userelationship-in-calculated-columns/

How to Get Your Question Answered Quickly

Regards,

Cherie

[Anonymous]

Thanks for your fast reply! I tried to use userelationship function but it didn't worked. Also, I tried the following solution provided in other thread:

https://community.powerbi.com/t5/Desktop/Count-rows-by-month-for-2-different-date-columns/m-p/495959...

I'll continue trying other possibilities. Any other ideas?

Thanks!

[v-cherch-msft]

Hi @Anonymous

Sample data and expected output will be helpful to provide an accurate solution.Could you follow this How to Get Your Question Answered Quickly to explain more about your expected output?You can upload the .pbix file to OneDrive and post the link here. Do mask sensitive data before uploading.

Regards,

Cherie

[Anonymous]

Hi @v-cherch-msft

Here you have the link to the PBI file:

https://econocom-my.sharepoint.com/:u:/p/miguel_grandioux/EelkrA-DbARMgcEZY30r_doBCg5gX63o7e5zDq4Mix...

There are 2 tables: Data (raw data with ticket number and OpenDate and CloseDate, ignore Creation and Resolution) and a table called Dates with the calendar and columns I want to count OpenDate and CloseDate (OpenCount and CloseCount respectively).

The expected output should be the following:

Date	OpenCount	CloseCount
01/11/2017	0	0
02/11/2017	10	11
03/11/2017	3	11
04/11/2017	0	0
05/11/2017	0	0
06/11/2017	11	8
07/11/2017	4	4
08/11/2017	10	9
09/11/2017	2	3
10/11/2017	15	12
11/11/2017	0	0
12/11/2017	0	0
13/11/2017	16	15
14/11/2017	6	6
15/11/2017	4	6
16/11/2017	12	13
17/11/2017	6	2
18/11/2017	0	0
19/11/2017	0	0

But you can that the formula countrows accumulates the data:

Date	OpenCount	CloseCount
01/11/2017	0	0
02/11/2017	10	11
03/11/2017	13	22
04/11/2017	13	22
05/11/2017	13	22
06/11/2017	24	30

I tried userelationship I didn't manage to get the correct count.

[v-cherch-msft · Accepted solution]

Hi @Anonymous

You may create measures or columns to get the count.Please refer to attached file.

CloseCountColumn =
CALCULATE (
    COUNT ( Data[TICKET NUMBER] ),
    USERELATIONSHIP ( Data[Resolution], Dates[Date] )
) + 0

Regards,

Cherie

[Anonymous]

Thanks a lot for your help!!!