Find resolved count earlier days

[Akshay123]

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.

[Anonymous]

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]

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 · Accepted solution]

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]

explain more about data table rather than your dax