Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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
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.
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 |
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 |
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 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |