cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mhorn30
Frequent Visitor

Date on Date comparison on record statuses

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:

 

ProgrammeCandidate IDStatusDate
2019 Programme101Applicant26/08/2018
2019 Programme101Completed29/08/2018
2019 Programme101Reviewed01/09/2018
2019 Programme102Applicant05/09/2018
2020 Programme202Applicant29/09/2019
2020 Programme202Completed01/10/2019
2020 Programme201Applicant28/08/2019
2020 Programme201Completed02/09/2019
2020 Programme201Reviewed05/09/2019
2020 Programme201Accepted10/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.

 

ProgrammeCandidate IDStatusDate
2019 Programme101Applicant26/08/2018
2019 Programme101Completed29/08/2018
2019 Programme101Reviewed01/09/2018
2019 Programme102Applicant05/09/2018
2020 Programme202Applicant29/09/2019
2020 Programme202Completed01/10/2019
2020 Programme201Applicant28/08/2019
2020 Programme201Completed02/09/2019
2020 Programme201Reviewed05/09/2019
2020 Programme201Accepted10/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!

5 REPLIES 5
Community Support
Community Support

Re: Date on Date comparison on record statuses

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.

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
Frequent Visitor

Re: Date on Date comparison on record statuses

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.

 

ProgrammeCandidate IDStatusDate
2019 Programme101Applicant26/08/2018
2019 Programme101Completed29/08/2018
2019 Programme101Reviewed01/09/2018
2019 Programme102Applicant05/09/2018
2020 Programme202Applicant29/09/2019
2020 Programme202Completed01/10/2019
2020 Programme201Applicant28/08/2019
2020 Programme201Completed02/09/2019
2020 Programme201Reviewed05/09/2019
2020 Programme201Accepted10/09/2019

 

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

 

ProgrammeCandidate IDStatusDate
2019 Programme101Applicant26/08/2018
2019 Programme101Completed29/08/2018
2019 Programme101Reviewed01/09/2018
2019 Programme102Applicant05/09/2018
2020 Programme202Applicant29/09/2019
2020 Programme202Completed01/10/2019
2020 Programme201Applicant28/08/2019
2020 Programme201Completed02/09/2019
2020 Programme201Reviewed05/09/2019
2020 Programme201Accepted10/09/2019
Super User IV
Super User IV

Re: Date on Date comparison on record statuses

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





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blog -Week is not so Weak Proud to be a Super User! Connect on Linkedin


mhorn30
Frequent Visitor

Re: Date on Date comparison on record statuses

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.

 

ProgrammeCandidate IDStatusDate
2019 Programme101Applicant26/08/2018
2019 Programme101Completed29/08/2018
2019 Programme101Reviewed01/09/2018
2019 Programme102Applicant05/09/2018
2020 Programme202Applicant29/09/2019
2020 Programme202Completed01/10/2019
2020 Programme201Applicant28/08/2019
2020 Programme201Completed02/09/2019
2020 Programme201Reviewed05/09/2019
2020 Programme201Accepted10/09/2019

 

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

 

ProgrammeCandidate IDStatusDate
2019 Programme101Applicant26/08/2018
2019 Programme101Completed29/08/2018
2019 Programme101Reviewed01/09/2018
2019 Programme102Applicant05/09/2018
2020 Programme202Applicant29/09/2019
2020 Programme202Completed01/10/2019
2020 Programme201Applicant28/08/2019
2020 Programme201Completed02/09/2019
2020 Programme201Reviewed05/09/2019
2020 Programme201Accepted10/09/2019
mhorn30
Frequent Visitor

Re: Date on Date comparison on record statuses

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.

 

ProgrammeCandidate IDStatusDate
2019 Programme101Applicant26/08/2018
2019 Programme101Completed29/08/2018
2019 Programme101Reviewed01/09/2018
2019 Programme102Applicant05/09/2018
2020 Programme202Applicant29/09/2019
2020 Programme202Completed01/10/2019
2020 Programme201Applicant28/08/2019
2020 Programme201Completed02/09/2019
2020 Programme201Reviewed05/09/2019
2020 Programme201Accepted10/09/2019

 

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

 

ProgrammeCandidate IDStatusDate
2019 Programme101Applicant26/08/2018
2019 Programme101Completed29/08/2018
2019 Programme101Reviewed01/09/2018
2019 Programme102Applicant05/09/2018
2020 Programme202Applicant29/09/2019
2020 Programme202Completed01/10/2019
2020 Programme201Applicant28/08/2019
2020 Programme201Completed02/09/2019
2020 Programme201Reviewed05/09/2019
2020 Programme201Accepted10/09/2019

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors