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.
Hi all,
I have created call trackers and reports from the data in the trackers, but I am really struggling to get something dynamic right in the reporting.
I am looking to assess different dates separately, in relation to how many 'propositions' are sent out within the date parameter. To estimate a proposition, I look at the sales stage (i.e., where the customer is in the selling journey) and compare it to the previous call, if there was one. If the customer has moved from 1 to 2/3/4 or if they have been called for the first time and are in 2/3/4 stage, they are counted as a prop. - I have already successfully done this for only the current day, but when I try to do it retrospectively, I am challenged by the fact the companies are called on different days so the 'current' and 'previous' calls change and I can't account for that.
I've tried to attach a sample bit of data, as if it was taken from a call tracker, but I can't see an attach button. So the table is as below:
Sales Manager | Company ID | Call Date | Sales Stage | Notes |
John | abcdef1 | 01/01/2021 | 1 | sadasfsafadfdf |
John | dcefgf1 | 01/01/2021 | 1 | sadasfsafasASadfdf |
John | abcdef1 | 01/01/2021 | 1 | sadasfsaSDSAFfadfdf |
John | aaaaaa1 | 01/01/2021 | 1 | sadasfsadsgsfadfdf |
John | bbbbbb1 | 01/01/2021 | 3 | sadasfsadgsdgfadfdf |
John | cccccc2 | 02/01/2021 | 2 | sadasfsasgfsgfadfdf |
John | ddddd1 | 02/01/2021 | 1 | sadasfsafasfgfdfdf |
John | eeeee2 | 02/01/2021 | 1 | sadasfsafsdgfsdadfdf |
John | aaabbbb1 | 02/01/2021 | 1 | sadasfsasdgsdgfadfdf |
John | adgawrg1 | 02/01/2021 | 1 | sadasfsasdgsdfadfdf |
John | eeeee2 | 03/01/2021 | 3 | sadasfsafsdgsdgdgdadfdf |
John | abcdef1 | 03/01/2021 | 3 | sadasfsafsdgdgsdadfdf |
John | aaaaaa1 | 03/01/2021 | 3 | sadasfsafsdgsdgadfdf |
John | dcefgf1 | 03/01/2021 | 3 | sadasfsafadfsdgsgdf |
John | abcdef1 | 03/01/2021 | 4 | sdgsdgsdg |
I would want the result to look as follows after PowerQuery:
Date | Propositions |
01/01/2021 | 1 |
02/01/2021 | 1 |
03/01/2021 | 4 |
It is ok if you don't understand my logic for propositions and if you can just help with the idea around being able to pull data from the most recent call and the ones before that - the issue is that each time the customer is called the 'most recent' and 'previous before that' calls change and I need to be able to identify these calls retrospectively, even if that customer has been called again since. E.g., if the customer was called 6 times, I need to pinpoint the date the 'prop' was sent (i.e., there can only be 1 occurence when it moves from stage 0/1 to a 2/3/4). This can include calls that occured on the same day.
This was really difficult to explain so let me know if you have any questions but any help would be greatly appreciated!! Many thanks in advance.
The logic I used to do this for the 'current date' version is by creating 2 tables (today and everyday before today) and then comparing today with previous days and flagging if someone is new or has moved a stage etc. But each day this resets and I need something more robust I can use to look at any date parameter or create a running linegraph with each day on it.
Solved! Go to Solution.
Please try this version instead. It gets the prev stage value from only the previous date. Note this does not address the scenario where two calls are placed on the same day. However, you can adapt this to work with an Index column (or time, or some other column that shows the call order) instead of the Call Date column.
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
The logic you are describing is more easily accomplished with a DAX measure (and a table visual to generate your desired output), especially to have it respond to slicers or calculate for each day. Here is a DAX measure that seems to work with your data.
Props =
VAR companydays =
CALCULATETABLE (
SUMMARIZE ( Calls, Calls[Company ID], Calls[Call Date] ),
Calls[Sales Stage] IN { 2, 3, 4 }
)
VAR prevstage =
ADDCOLUMNS (
companydays,
"cPrevStage",
VAR thisdate = Calls[Call Date]
RETURN
CALCULATE (
MIN ( Calls[Sales Stage] ),
ALL ( Calls[Call Date] ),
Calls[Call Date] < thisdate
)
)
RETURN
COUNTROWS (
FILTER ( prevstage, OR ( ISBLANK ( [cPrevStage] ), [cPrevStage] IN { 0, 1 } ) )
)
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Actually, looking further into it I am not sure if this is correct. It looks to be showing anyone who at some point was in stage 0 or 1 but it must be that they were in stage 0 or 1 in the call before this one. So e.g., if they moved from 1 to 2 on day X, this is the day the prop was made. If they moved from a 2 to 3 at any point after that or had a new call with stage 2 again - this should not could as a prop again.
Does that make sense? Looking at the DAX, I can't see what any part of it is pulling the call before this one, and more like all calls before this one.
Please try this version instead. It gets the prev stage value from only the previous date. Note this does not address the scenario where two calls are placed on the same day. However, you can adapt this to work with an Index column (or time, or some other column that shows the call order) instead of the Call Date column.
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Wow thank you this is amazing. Are you able to explain how you did it? As I will need to translate it for the real document and I'd love to use the same logic to get other aspects (e.g., the number of calls that had the potential to be a prop, so I can then get the prop % per sales manager. This would be done likely with various filters that I didn't work into my sample [DM avaivable flag and call only flag] and with either new calls or calls that don't have the same stage as the last call).
Thanks again!! I am so impressed, I wasn't even sure if I made sense asking the question haha
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.