Cumulative count based on open and closed date

[Anonymous]

I have data that represents tickets in a support system, for the sake of simplicity consider 4 columns, ID, Date Opened, Date Closed, Status.

What I'm trying to achieve is to have a table that shows:

1. Tickets opened per month
2. Tickets closed per month
3. Cumulative tickets open by month
4. Cumulative tickets closed by month

any help or suggestions greatly appreciated, I could do this easily in Excel but I'm relatively new to BI / Dax.

[v-diye-msft]

Hi @Anonymous ,

I’ve created a table as below, the status will show Off once ticket closed.  Pbix attached here for your reference: https://wicren-my.sharepoint.com/:u:/g/personal/dinaye_wicren_onmicrosoft_com/ET_34Gzt7mlPj0lSAgH_SMUBo6jOMXICBteSkCtetScB8A?e=kOibQc

Please refer to following formulas to generate the results:

1. Create MonthOpen and MonthClosed column :

MO = MONTH([DateOpened])
MONTH([DateClosed])

2. Measures as listed :

Tickets opened by month = CALCULATE(SUMX(Table1,DISTINCTCOUNT(Table1[MO])),ALLEXCEPT(Table1,Table1[MO]))
Tickets closed by month = IF(MAX([MC])=BLANK(),BLANK(),CALCULATE(SUMX(Table1,DISTINCTCOUNT(Table1[MC])),ALLEXCEPT(Table1,Table1[MC])))
Cumulative tickets open by month = CALCULATE(DISTINCTCOUNT(Table1[ID]),FILTER(ALL(Table1),Table1[DateOpened]<=MAX(Table1[DateOpened])),VALUES(Table1[MO]))
Cumulative tickets closed by month = IF(MAX([DateClosed])=BLANK(),BLANK(),CALCULATE(DISTINCTCOUNT(Table1[ID]),FILTER(ALL(Table1),Table1[DateOpened]<=MAX(Table1[DateOpened])),VALUES(Table1[MC])))

Best regards,

Dina Ye

[Anonymous]

Hi @v-diye-msft 

I've given it a try but some of the measures don't seem to be working correctly:

Just to clarify what i'm tryig to end up with is something like this:

But the actual data contains tickets raised across multiple years.

I hope that makes is a bit clearer as to what I am trying to achieve.

Thanks for the support so far - Alex

[Anonymous]

Dina,

Thank you that's lots of help and has taught me lots, how would I expand this to cope with that data that spans more than one year?

Thanks

[v-diye-msft · Accepted solution]

Hi @Anonymous ,

1. Create the yr column:

YO= Year([DateOpened])
YC= Year([DateClosed])

2. Measures as listed :

Tickets opened by year = CALCULATE(SUMX(Table1,DISTINCTCOUNT(Table1[YO])),ALLEXCEPT(Table1,Table1[YO]))
Tickets closed by year = IF(MAX([YC])=BLANK(),BLANK(),CALCULATE(SUMX(Table1,DISTINCTCOUNT(Table1[YC])),ALLEXCEPT(Table1,Table1[YC])))
Cumulative tickets open by year = CALCULATE(DISTINCTCOUNT(Table1[ID]),FILTER(ALL(Table1),Table1[DateOpened]<=MAX(Table1[DateOpened])),VALUES(Table1[YO]))
Cumulative tickets closed by year = IF(MAX([DateClosed])=BLANK(),BLANK(),CALCULATE(DISTINCTCOUNT(Table1[ID]),FILTER(ALL(Table1),Table1[DateOpened]<=MAX(Table1[DateOpened])),VALUES(Table1[YC])))

Best regards,

Dina Ye

[v-diye-msft]

Hi @Anonymous 

If my above post helps, could you please consider Accept it as the solution to help the other members find it more quickly. thanks!

Best regards,

Dina Ye