var maxValue =
CALCULATE(
MAX('View_AllHelpToolRequestsWithAudit (2)'[AuditCreatedDate]),
ALLEXCEPT('View_AllHelpToolRequestsWithAudit (2)','View_AllHelpToolRequestsWithAudit (2)'[Id]
),
DATESBETWEEN('View_AllHelpToolRequestsWithAudit (2)'[AuditCreatedDate],[Start Of Previous Period],[End Of Previous Period]),
DATESBETWEEN(Dim_Date[Date],[Start Of Previous Period],[End Of Previous Period]),
'View_AllHelpToolRequestsWithAudit (2)'[AuditNewStatusId] <> 3 || 'View_AllHelpToolRequestsWithAudit (2)'[AuditNewStatusId] <> 4 ,
'View_AllHelpToolRequestsWithAudit (2)'[AuditIsActive] = TRUE()
)
VAR colmax= MAXX('View_AllHelpToolRequestsWithAudit (2)','View_AllHelpToolRequestsWithAudit (2)'[AuditCreatedDate])
return
COUNTX(FILTER(VALUES('View_AllHelpToolRequestsWithAudit (2)'[Id]),maxValue <=colmax),'
View_AllHelpToolRequestsWithAudit (2)'[Id])
-----------------------------------------------------------------------------------------------------
I want to check only those records whose latest AuditCreated date range is between date range [Start Of Previous Period] to [End Of Previous Period]
---------------------------------------------------------------------------------------------------------------------------
Consider below scenario:
slicer date range - 11 june to 11 july
ID AuditCreatedDate Status IsActive
1 12 June open True
1 11 June open True ---- max (+1)
1 10 June open True
2 12 Aug open True
2 10 July open True --- max (+1)
2 9 June open True
3 13June open True
3 10 June open True -- ---max(+1)
3 6 June open True
Ans: Previous Open ticket count = 3
