Dynamic reporting from call data

[Izzii0x]

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.

[mahoneypat]

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

[Izzii0x]

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.

[mahoneypat · Accepted 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.

Props2 =

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]

VAR prevdate = CALCULATE(MAX(Calls[Call Date]), all(Calls[Call Date]), Calls[Call Date]<thisdate)

RETURN

CALCULATE(

MIN( Calls[Sales Stage] ),

ALL( Calls[Call Date] ),

Calls[Call Date] = prevdate

)

)

RETURN

COUNTROWS(

FILTER( prevstage, OR( ISBLANK( [cPrevStage] ), [cPrevStage] IN { 0, 1 } ) )

)

Pat

[Izzii0x]

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