Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Aim : To count previous open ticket - We want to count all distinct ticket ID having dates before slicer selected date (such that we only take the latest Auditcreated date of Ticket ID before slicerselected date) with below filters
Status column - should not be closed or removed
IsActive column should be - true
AuditCreatedDate - should be before slicer selected date
CreatedDate - should be before slicer selected date
IMP : We should only consider latest AuditCreatedDate "before slicer selected date".
Note: I have created measure for date before slicer selected date as
Explanation:
Consider below 1st scenario:
slicer - 13 june to 12 july
ID AuditCreatedDate Status IsActive
1 12 June open True ----- max(+1)
1 11 June close True
1 10 June close True
2 11 June open True ----- max (+1)
2 10 June open True
2 9 June close True
3 7 June close False
3 6 June open True
Ans: Previous Open ticket count = 2
------------------------------------------------------------------------------------------
Consider below 2nd scenario:
slicer - 12 june to 12 july
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
@ashwini12 , Try a measure like
measure =
var _max = maxx(filter(allselected(Table), [ID] = max(Table[ID])), [AuditCreatedDate])
var _1 = maxx(allselected(Date), Date[Date])
return
countx(filter(values(Table[ID]),_max <= _1), [ID])
@amitchandak The solution provided by you gives maximum date in the entire table, requirement is different.
In the below measure we want to give only latest AuditCreated dates per ID between date range [Start Of Previous Period] and [End Of Previous Period].
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |