I have a data set with four fiscal years. I need two KPIs, one with new patients and the other with returning patients.
I began with creating a fiscal calendar with flags for currentFY. To get new patients I used the count(distinct(PatientID)) which gave me the number instances new patients came into the hospital system through all the four years.
Now I need to filter out the new patients for the currentFY.
If the formula filters the unique IDs first and then by year then it would give the true unique IDs within the whole dataset.
If it filters the year and then by unique IDs; this would give unique IDs only in that year. But the patient with that ID could have come the previous year. In such a case its a new entry only to that year not new to the system and I need to count the new patients entering to the system.