Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Cumulative count based on open and closed date

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.
Need Help
  • v-diye-msft's avatar
    v-diye-msft
    7 years ago

    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

     

5 Replies

  • v-diye-msft's avatar
    v-diye-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        v-diye-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        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

         

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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