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

Dynamic reporting from call data

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 ManagerCompany IDCall DateSales StageNotes
Johnabcdef101/01/20211sadasfsafadfdf
Johndcefgf101/01/20211sadasfsafasASadfdf
Johnabcdef101/01/20211sadasfsaSDSAFfadfdf
Johnaaaaaa101/01/20211sadasfsadsgsfadfdf
Johnbbbbbb101/01/20213sadasfsadgsdgfadfdf
Johncccccc202/01/20212sadasfsasgfsgfadfdf
Johnddddd102/01/20211sadasfsafasfgfdfdf
Johneeeee202/01/20211sadasfsafsdgfsdadfdf
Johnaaabbbb102/01/20211sadasfsasdgsdgfadfdf
Johnadgawrg102/01/20211sadasfsasdgsdfadfdf
Johneeeee203/01/20213sadasfsafsdgsdgdgdadfdf
Johnabcdef103/01/20213sadasfsafsdgdgsdadfdf
Johnaaaaaa103/01/20213sadasfsafsdgsdgadfdf
Johndcefgf103/01/20213sadasfsafadfsdgsgdf
Johnabcdef103/01/20214sdgsdgsdg

 

 

I would want the result to look as follows after PowerQuery:

DatePropositions
01/01/20211
02/01/20211
03/01/20214

 

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.

1 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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

4 REPLIES 4
mahoneypat
Microsoft
Microsoft

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.

 

mahoneypat_0-1646179762145.png

 

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI 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.

 

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI 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

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors