none
DAX Query Question - Calculated Measure RRS feed

  • Question

  • Essentially I need to create a calculated measure that determines the average days open of all open tickets on a certain date.  For example if there was a ticket opened on April 10<sup>th</sup> and April 20<sup>th</sup>, then on April 30<sup>th</sup> the average days open would be 15.  I’m not sure how to create this measure.

     

    More specifically, I would like to find the Average days open for all tickets that meet this criteria in my sheet.  However, the average days open needs to be the Average on the End of Month.

    m Open Tickets = IF(COUNTROWS(VALUES('Sheet1 (2)'[Month]))=1, COUNTROWS(FILTER(IncidentSet, IncidentSet[Ticket Date (New)] <= VALUES('Sheet1 (2)'[End of Month]) && (IncidentSet[Ticket Date (Closed)] > VALUES('Sheet1 (2)'[End of Month]) || [StateCode.Value] = 0))))

     

    Any ideas?

     

    -Nick


    • Edited by nmartinovic Wednesday, August 12, 2015 9:05 PM
    Wednesday, August 12, 2015 8:16 PM

Answers

  • Nick,

    Could you clarify the definitions of the measures in question? For instance, your sample data shows Ticket #1 was opened on 4/5 and closed on 4/20 with a "Days Open" of 15. However, in your expected output, you should 0 tickets open for more than 14 days in April.

    With respect to Average Days Open, it's again unclear where the expected number is coming from. It looks like you may be representing "For tickets that are still open at the end of Month X, what's the average number of days they've been open?" Is that correct?

    Taylor Clark


    Tuesday, September 8, 2015 7:43 PM

All replies

  • Any chance you can post some sample data?

    Thursday, August 13, 2015 5:16 AM
  • Hi Seth,

    Thanks for taking a look in to this.  Basically, I've attached an excel sheet.  The raw data is on the left and that is how my sheet looks.  I need to transform it to what's on the right using calculated measures.  I've already figured out everything except how to determine the Average Days Open and the Tickets Open > 14 days.

    http://1drv.ms/1JVXZwZ

    Thanks,

    Nick

    Thursday, August 13, 2015 4:15 PM
  • Nick,

    Could you clarify the definitions of the measures in question? For instance, your sample data shows Ticket #1 was opened on 4/5 and closed on 4/20 with a "Days Open" of 15. However, in your expected output, you should 0 tickets open for more than 14 days in April.

    With respect to Average Days Open, it's again unclear where the expected number is coming from. It looks like you may be representing "For tickets that are still open at the end of Month X, what's the average number of days they've been open?" Is that correct?

    Taylor Clark


    Tuesday, September 8, 2015 7:43 PM