Hi all, hope you can assist me with this issue.
I have a dataset consisting of some of the following fields that show non conformance IDs against Departments and the status.
Department | NC_ID | Created_Date | Status | Closure_Date
Status could be 'In-Progress' or 'Closed'
I understand how to create charts that show in the moment statuses of NCs in different departments. The way we work however, we would like to see data for 2022 or 2021 also. So, I created a filter and is filtering on;
=[Created_Date.autoCalendar.Year]
Problem is that NCs that was both raised and closed in same selected time filter will be displayed twice, and if I use condition on status, they will come as closed even if it was open in 2022
Measure for Closed: =Count([Closure_Date])
Measure for In-Progress: =if(IsNull([Closure_Date]),Count(Created_Date), Null() )
So what i want, is to reference the active selection (filter) somehow;
Measure for In-Progress: = IF(Closure_Date is within active selection of Created_Date), Null(), Count(Created_Date))
I think im looking at this issue from the wrong angle, that im not going about it in the right way. Can anybody help me out?
Thank you