Forum Discussion

jblane's avatar
jblane
Frequent Visitor
8 years ago
Solved

Cumulative Totals w/Multiple Dates

Hello Gurus!

 

I'm trying to produce a chart showing, the number of created and resolved tickets by month with a cumulative total of the tickets still open.

 

My dataset has 2 date columns to provide the dates the tickets were created and resolved. I did create a date table (autodate).

Below are the formulas I'm using to get the count of tickets created and resolved.  Any help in getting the formula for the monthly cumulative count of open (created but unresolved tickets) is appreciated.

 

# Created = COUNT(Report[Created])

# Resolved = CALCULATE([Resolved Count], USERELATIONSHIP(Report[RR], 'Date Table'[Date]))

# Created minus # Resolved = [# Created] - [# Resolved]   

 

Many Thanks,
Jason

 

Need Help
  • jblane's avatar
    jblane
    8 years ago

    Thanks for the input.  Unfortunately, those options didn't help, but did get me thinking of another solution.

     

    Here's what I did:

     

    1) Added a Month/Year grouping to my date table.  This was key in getting the counts to roll up on a monthly basis.

    2) Created a measure to capture the monthly count of open defects (Open 1 = [Defects Opened]-[Defects Closed])

    3) Then used the the "New Quick Measure" feature to get the running total 

    Total Open = CALCULATE([Open 1],FILTER(ALLSELECTED('Date Table'[Month/Year]),ISONORAFTER('Date Table'[Month/Year], MAX('Date Table'[Month/Year]), DESC)))

     

     

    The running count total needs to carry over from one year to the next (i.e. the open count in Dec 2017 needs to carry over to Jan 2018).  The running count total for open defects works annually, but does not allow carryover. 

     

    Any solutions for allowing a running total to span multiple years?  Solved:   Removed the Year Visual Filter

3 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    Hi jblane,

     

    You can create a measure using DAX formula below and check if it can meet your requirement:

     

     

     

    # Created minus # Resolved =
CALCULATE (
    COUNT ( Report[Created] ) - [Resolved Count],
    ALLEXCEPT ( 'Date Table', 'Date Table'[Date] )
)

    Regards,

    Jimmy Tao

  • jblane's avatar
    jblane
    Frequent Visitor
    8 years ago

    Thanks for the input.  Unfortunately, those options didn't help, but did get me thinking of another solution.

     

    Here's what I did:

     

    1) Added a Month/Year grouping to my date table.  This was key in getting the counts to roll up on a monthly basis.

    2) Created a measure to capture the monthly count of open defects (Open 1 = [Defects Opened]-[Defects Closed])

    3) Then used the the "New Quick Measure" feature to get the running total 

    Total Open = CALCULATE([Open 1],FILTER(ALLSELECTED('Date Table'[Month/Year]),ISONORAFTER('Date Table'[Month/Year], MAX('Date Table'[Month/Year]), DESC)))

     

     

    The running count total needs to carry over from one year to the next (i.e. the open count in Dec 2017 needs to carry over to Jan 2018).  The running count total for open defects works annually, but does not allow carryover. 

     

    Any solutions for allowing a running total to span multiple years?  Solved:   Removed the Year Visual Filter