Date on Date comparison on record statuses

[mhorn30]

I have the data of each status that an applicant has gone through as well as the data of the status change.

I want to be able to compare the number of applicants per status between a date last year vs. this year

As an example:

Programme	Candidate ID	Status	Date
2019 Programme	101	Applicant	26/08/2018
2019 Programme	101	Completed	29/08/2018
2019 Programme	101	Reviewed	01/09/2018
2019 Programme	102	Applicant	05/09/2018
2020 Programme	202	Applicant	29/09/2019
2020 Programme	202	Completed	01/10/2019
2020 Programme	201	Applicant	28/08/2019
2020 Programme	201	Completed	02/09/2019
2020 Programme	201	Reviewed	05/09/2019
2020 Programme	201	Accepted	10/09/2019

I challenge is that I can't just work with the date column. I need to also include the latest status per applicant as a status could have updated prior to the date selected for the report.

E.g. If I choose the data "01 September" it should show the following data in green, as this is the latest status reason per candidate on or before this date.

Programme	Candidate ID	Status	Date
2019 Programme	101	Applicant	26/08/2018
2019 Programme	101	Completed	29/08/2018
2019 Programme	101	Reviewed	01/09/2018
2019 Programme	102	Applicant	05/09/2018
2020 Programme	202	Applicant	29/09/2019
2020 Programme	202	Completed	01/10/2019
2020 Programme	201	Applicant	28/08/2019
2020 Programme	201	Completed	02/09/2019
2020 Programme	201	Reviewed	05/09/2019
2020 Programme	201	Accepted	10/09/2019

My logic says I need something like a calculated columns with a paramter (which is the date chosen by the user), which then finds the latest record per candidate on or before the parameter date (or indicate a True/False for each record), which I can then use to create a visual per programme per status reason.

I'm just not sure exactly how to tackle this/where to start.

Any suggestions would be appreciated, thank you!

[amitchandak]

Please check this link, if it can help. There are two solutions. For the second one, there is a link in the article

https://medium.com/chandakamit/power-bi-comparing-data-across-date-ranges-36be49b68613

[v-xuding-msft]

Hi @mhorn30 ,

I'm confused about your requirement. You want to compare the number of applicants per status between a date last year vs. this year. Moreover, you need to based on the selected date and different status per candidate, right? I can't understand why show the Candidate ID 201 in green when you choose "01 September"?  Are the 101 and the 201 the same candidate? Can you please share more details for us?

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mhorn30]

Hi @v-xuding-msft 

Candidate 201 "Applicant" status is green as this is the latest status update for that candidate on or before 01 September.

The next status for Candidate 201 is "Completed" which was only set on the 02 September. So if I compare YoY according to a specific date (01 Sept in this case), this status has not happened yet.

Candidate ID's are unique, so 101 and 201 is not the same user.

If I was to select 01 October as my comparison data YoY, then these would be the statusses per candidate that was returned as they are the latest status on or before 01 October.

Programme	Candidate ID	Status	Date
2019 Programme	101	Applicant	26/08/2018
2019 Programme	101	Completed	29/08/2018
2019 Programme	101	Reviewed	01/09/2018
2019 Programme	102	Applicant	05/09/2018
2020 Programme	202	Applicant	29/09/2019
2020 Programme	202	Completed	01/10/2019
2020 Programme	201	Applicant	28/08/2019
2020 Programme	201	Completed	02/09/2019
2020 Programme	201	Reviewed	05/09/2019
2020 Programme	201	Accepted	10/09/2019

If I was to select 29 August, then only these statusses per candidate will be returned:

Programme	Candidate ID	Status	Date
2019 Programme	101	Applicant	26/08/2018
2019 Programme	101	Completed	29/08/2018
2019 Programme	101	Reviewed	01/09/2018
2019 Programme	102	Applicant	05/09/2018
2020 Programme	202	Applicant	29/09/2019
2020 Programme	202	Completed	01/10/2019
2020 Programme	201	Applicant	28/08/2019
2020 Programme	201	Completed	02/09/2019
2020 Programme	201	Reviewed	05/09/2019
2020 Programme	201	Accepted	10/09/2019

[mhorn30]

Hi @v-xuding-msft 

Candidate 201 "Applicant" status is green as this is the latest status update for that candidate on or before 01 September.

The next status for Candidate 201 is "Completed" which was only set on the 02 September. So if I compare YoY according to a specific date (01 Sept in this case), this status has not happened yet.

Candidate ID's are unique, so 101 and 201 is not the same user.

If I was to select 01 October as my comparison data YoY, then these would be the statusses per candidate that was returned as they are the latest status on or before 01 October.

Programme	Candidate ID	Status	Date
2019 Programme	101	Applicant	26/08/2018
2019 Programme	101	Completed	29/08/2018
2019 Programme	101	Reviewed	01/09/2018
2019 Programme	102	Applicant	05/09/2018
2020 Programme	202	Applicant	29/09/2019
2020 Programme	202	Completed	01/10/2019
2020 Programme	201	Applicant	28/08/2019
2020 Programme	201	Completed	02/09/2019
2020 Programme	201	Reviewed	05/09/2019
2020 Programme	201	Accepted	10/09/2019

If I was to select 29 August, then only these statusses per candidate will be returned:

Programme	Candidate ID	Status	Date
2019 Programme	101	Applicant	26/08/2018
2019 Programme	101	Completed	29/08/2018
2019 Programme	101	Reviewed	01/09/2018
2019 Programme	102	Applicant	05/09/2018
2020 Programme	202	Applicant	29/09/2019
2020 Programme	202	Completed	01/10/2019
2020 Programme	201	Applicant	28/08/2019
2020 Programme	201	Completed	02/09/2019
2020 Programme	201	Reviewed	05/09/2019
2020 Programme	201	Accepted	10/09/2019

[mhorn30]

Hi @v-xuding-msft 

Candidate 201 "Applicant" status is green as this is the latest status update for that candidate on or before 01 September.

The next status for Candidate 201 is "Completed" which was only set on the 02 September. So if I compare YoY according to a specific date (01 Sept in this case), this status has not happened yet.

Candidate ID's are unique, so 101 and 201 is not the same user.

If I was to select 01 October as my comparison data YoY, then these would be the statusses per candidate that was returned as they are the latest status on or before 01 October.

Programme	Candidate ID	Status	Date
2019 Programme	101	Applicant	26/08/2018
2019 Programme	101	Completed	29/08/2018
2019 Programme	101	Reviewed	01/09/2018
2019 Programme	102	Applicant	05/09/2018
2020 Programme	202	Applicant	29/09/2019
2020 Programme	202	Completed	01/10/2019
2020 Programme	201	Applicant	28/08/2019
2020 Programme	201	Completed	02/09/2019
2020 Programme	201	Reviewed	05/09/2019
2020 Programme	201	Accepted	10/09/2019

If I was to select 29 August, then only these statusses per candidate will be returned:

Programme	Candidate ID	Status	Date
2019 Programme	101	Applicant	26/08/2018
2019 Programme	101	Completed	29/08/2018
2019 Programme	101	Reviewed	01/09/2018
2019 Programme	102	Applicant	05/09/2018
2020 Programme	202	Applicant	29/09/2019
2020 Programme	202	Completed	01/10/2019
2020 Programme	201	Applicant	28/08/2019
2020 Programme	201	Completed	02/09/2019
2020 Programme	201	Reviewed	05/09/2019
2020 Programme	201	Accepted	10/09/2019