Forum Discussion

Akshay123's avatar
Akshay123
Helper 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)'[sys_created_on] >=DATE(2024,1,1)))
Count Resolved = 
COUNTROWS(FILTER('incident (2)', 'incident (2)'[resolved_at] >=DATE(2024,1,1) && 'incident (2)'[resolved_at] <DATE(2024,5,4) && 'incident (2)'[sys_created_on] >= DATE(2024,1,1)))



Below resolved count is till 3rd May eod

 

Below coount is till 2nd May eod

 

Below count is till 1st May eod

 

If we absorb resolved count is getting changed whenever we change the dates, I need the dynamic values for last 7 days of the count of max resolved date.

 

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)))

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    explain more about data table rather than your dax

  • 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
      Helper 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)))