Forum Discussion

jarod's avatar
jarod
New Member
10 years ago

How do I filter on tickets closed last week?

I'm working on building a dashboard for an IT Helpdesk.  I'm wanting a measure for First Call Resolution (Last Week).  How can I filter on a table column that has duplicate date fields(i.e. something like PREVIOUSWEEK(incident_metric[inc_closed_at]))?  This is all I have so far... which works but does not filter on last week's tickets only:

 

First Call Resolution (Last Week): = CALCULATE(COUNTA(incident_metric[mi_value]),incident_metric[mi_value]="true")/COUNTA(incident_metric[mi_value])

 

 

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago

    Hi jarod,

     

    >>How can I filter on a table column that has duplicate date fields(i.e. something like PREVIOUSWEEK(incident_metric[inc_closed_at]))?

     

    In DAX, there is no PREVIOUSWEEK() function. In your scenario, you can use DateAdd function to achieve your requirement:

     

    Dax: (I modified your formula)

    First Call Resolution (Last Week): = CALCULATE(COUNTA(incident_metric[mi_value]),Filter(incident_metric,AND(incident_metric[mi_value]="true", incident_metric[inc_closed_at]  = DATEADD(incident_metric[inc_closed_at],-7,DAY) )/COUNTA(incident_metric[mi_value])

     

    Regards,

    Xiaoxin Sheng

  • jarod's avatar
    jarod
    New Member
    10 years ago

    Not sure why you are marking that as an accepted solution.  It was not a solution.

     

    DATEADD(incident_metric[inc_closed_at],-7,DAY) - This only counts days 7 days earlier than today's date.  If today is Thursday, I would get data from last Friday through today.  This is not what I asked.  I'm looking for something that will get dates for last week (i.e. last Sunday through last Saturday).

    • Anonymous's avatar
      Anonymous
      Not applicable
      10 years ago

      Hi jarod,

       

      Since you didn’t provide more detail information about your requirement, I create a simple sample to test, you can follow below steps:

       

      test table.

       

      Dax formula:

       

      distinct without lastweek =

      UNION(

      DISTINCT(FILTER(Test,OR(Test[Date]< MAX(Test[Date]) -7, Test[Date] >MAX(Test[Date])))),

      FILTER(Test,AND(Test[Date]>= MAX(Test[Date]) -7, Test[Date] <=MAX(Test[Date])))

      )

       

      Result:

       

      Comment:

      FILTER(Test,OR(Test[Date]< MAX(Test[Date]) -7, Test[Date] >MAX(Test[Date]))) //filter the records not in last week

      DISTINCT(), // remove the duplicate records.

      FILTER(Test,AND(Test[Date]>= MAX(Test[Date]) -7, Test[Date] <=MAX(Test[Date]))) //filter the records in last week

      UNION( ) //merge the result records.

       

      If above doesn’t meet your requirement, please share some sample data and expected results for our analysis.

       

      Reference:

      DISTINCT Function (DAX)

      UNION Function (DAX)

       

      Regards,

      Xiaoxin Sheng