Forum Discussion

Akshay123's avatar
Akshay123
Icon for Helper I rankHelper I
2 years ago
Solved

Find resolved count earlier days

Hello Experts I need to find out a ticket active counts, created - resolved on that till day. I have used below logics to find  Craeted Count = COUNTROWS(FILTER('incident (2)', 'incident (2)'...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Akshay123 ,

    Regarding your question, are you trying to calculate dates based on the first 7 days on the x-axis? For example, if the x-axis is '5/1/2024' and '5/2/2024', the expected result would be to calculate the total number of problems solved in the first 7 days of May 1 and May 2, respectively?

    Use the following DAX expression to create a measure.

    Measure 2 = 
VAR _a = MAX('incident(2)'[resolved_at])
VAR _b = CALCULATE(COUNTROWS('incident(2)'),FILTER(ALL('incident(2)'),
'incident(2)'[resolved_at] >= _a -6  && 'incident(2)'[resolved_at] <= _a && 'incident(2)'[sys_created_on] >= DATE(2024,1,1)))
RETURN _b

    If you just want to count the number of problems solved in the first seven days from today, use the following dax expression.

    Measure 3 = CALCULATE(COUNTROWS('incident(2)'),FILTER(ALL('incident(2)'),
'incident(2)'[resolved_at] >= TODAY() -6 && 'incident(2)'[resolved_at] <= TODAY() && 'incident(2)'[sys_created_on] >= DATE(2024,1,1)))
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Anonymous ,thanks for the quick reply, I'll add further.

Hi Akshay123 ,

The Table data is shown below:


Please follow these steps:
1. Use the following DAX expression to create a table

Table = ADDCOLUMNS(CALENDAR(DATE(2024,1,1),DATE(2024,12,31)),"Month",MONTH([Date]),"Day",DAY([Date]))

2.Use the following DAX expression to create a measure

Measure = 
VAR _a = CALCULATE(MAX('Table'[Date]),ALL('Table'[Month],'Table'[Day]))
VAR _b = COUNTROWS(FILTER('incident(2)','incident(2)'[resolved_at] >= _a - 6 && 'incident(2)'[resolved_at] <_a &&'incident(2)'[sys_created_on] >= DATE(2024,1,1)))
RETURN _b

 3.Final output

 

 

  • Akshay123's avatar
    Akshay123
    Icon for Helper I rankHelper I
    2 years ago

    Hi Anonymous 

    Partially it works fine, but my requirement is I need to show last 7 days of reuslt in a line graph, which means last 7 days of data to be static.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Akshay123 ,

      Regarding your question, are you trying to calculate dates based on the first 7 days on the x-axis? For example, if the x-axis is '5/1/2024' and '5/2/2024', the expected result would be to calculate the total number of problems solved in the first 7 days of May 1 and May 2, respectively?

      Use the following DAX expression to create a measure.

      Measure 2 = 
VAR _a = MAX('incident(2)'[resolved_at])
VAR _b = CALCULATE(COUNTROWS('incident(2)'),FILTER(ALL('incident(2)'),
'incident(2)'[resolved_at] >= _a -6  && 'incident(2)'[resolved_at] <= _a && 'incident(2)'[sys_created_on] >= DATE(2024,1,1)))
RETURN _b

      If you just want to count the number of problems solved in the first seven days from today, use the following dax expression.

      Measure 3 = CALCULATE(COUNTROWS('incident(2)'),FILTER(ALL('incident(2)'),
'incident(2)'[resolved_at] >= TODAY() -6 && 'incident(2)'[resolved_at] <= TODAY() && 'incident(2)'[sys_created_on] >= DATE(2024,1,1)))