Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Count Create and End Dates in columns

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!!!! :)

Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    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

6 Replies