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])
The above measure is not working as other filters along with ALL Except are not working . Is there any other way to filter the table by above column filters and the Group by ID ?
----------------------------------------------------------------------------------------------------------------
Consider below scenario:
slicer - 12 june to 12 july
We need to check all records before 12 June (created a measure for this - between [start of previous period] and [end of previous period])
ID AuditCreatedDate Status IsActive
1 12 June open True {should not consider this record as AuditedCreatedDate should be before 12 June }
1 11 June open True ---- max (+1)
1 10 June close True
2 11 June close True {should not consider this record as status is close}
2 10 June open True
2 9 June close False
3 13June open True {should not consider this record as AuditedCreatedDate should be before 12 June }
3 10 June open True -- ---max(+1)
3 6 June open False
Ans: Previous Open ticket count = 2
