Thanks for the quick response. After I read your question I just realised I messed up in describing the source data structure. Apologies for the confusion.
I had another look at the source data and understood that the Patient ID field is distinct, therefore only one record per patient. Source data structure below (I removed the Status field as it is irrelevant to avoid further confusion) -
So yes, it is possible to have 3 non-null values in all the 3 date columns for a single row. For example, a patient could be Active on 1-Oct-2018, then Discharged 12-Dec-2018 and then Dropped 15-Feb-2019.
And after transformation the above data should look like this -
Status | Month | Count of Patients
Active | Oct-2018 | 2
Active | Nov-2018 | 1
Discharged | Dec-2018 | 1
Discharged | Jan-2019 | 2
Dropped | Feb-2019 | 2
To your second question, the original date format I receive in the source data is dd/mm/yyyy.
Thank you @Ashish_Mathur! I tried your PBIX file and it worked perfectly as per my question, so marking your response as solution.
I just have one more scenario that I am struggling with and will be great if you can help with this one as well. There are situations where DischargeDate and DroppedDate happens during the same month, in which case the same patient is counted twice (Discharged and Dropped separately) during the same month. Is there any way to work around this so that the patient is counted only once as dropped (dropped takes precendence)? Will also work if the later/higher date takes precedence. For instance, DischargedDate = 12 Jul 2019 and DroppedDate = 23 Jul 2019. In this scenario this patient should be counted as Dropped in July.