How to check if a client become quoted?

[timazarj]

Hello,

I have a sample table summarizing clients: I need to know how many of them became quoted within the last 4 months ( Users filter the report via date slicer in relationship with LogDate).

ClientId	ClientLog	LogDate
2686	New	2022-11-10
2686	Rick Johnson	2022-11-10
13030	New	2019-05-21
13030	Contact Attempt 1	2019-05-27
13030	Contact Attempt 2	2019-07-19
13030	Requote	2019-07-29
13030	New	2022-10-18
13030	Rick Johnson	2022-10-18
13030	Contact Attempt 1	2022-10-24
13030	Contact Attempt 2	2022-10-28
13030	Contact Attempt 3	2022-11-02
13030	Nurture	2022-11-07
37661	Quoted	2020-02-04
37661	Quote Follow-up 1	2020-02-21
37661	Quote Follow-up 2	2020-02-24
37661	Quote Follow-up 3	2020-02-27
37661	Nurture	2020-03-09
37661	New	2022-10-24
37661	Rick Johnson	2022-10-24
37661	Contact Attempt 1	2022-10-28
37661	Contact Attempt 2	2022-10-31
37661	Contact Attempt 3	2022-11-02
37661	Nurture	2022-11-03
67206	Contacted/Call Back	2022-09-26
67206	Quoted	2022-09-28
67206	Archive	2022-10-03
67480		2022-10-05
67480	Courtney Elliott	2022-10-05
67480	Emily Clarke	2022-10-05
67480	Requote	2022-10-05
67480	Rick Johnson	2022-10-05
69922	Policy Sold	2022-10-21
69925	Emily Clarke	2022-10-21
69925	Rick Johnson	2022-10-21
69925	Do Not Call	2022-11-01
69933	Kim George	2022-10-21
69933	Quoted	2022-10-21
69933	Rick Johnson	2022-10-21
69933		2022-10-24
69933	Quote Follow-up 1	2022-10-24
69933		2022-10-27
69933	Quote Follow-up 2	2022-10-27
69933		2022-10-31
69933	Quote Follow-up 3	2022-10-31
69933		2022-11-04
69933	Quote Follow-up 4	2022-11-04

 I need the result below: 

ClientId	Lead Become Quoted Within Last four months(LogObject=Quoted)
2686	0
13030	0
13030	0
37661	0	it became "Quoted" before the last 4 months
67206	1	it became "Quoted" in Sep, last four month
67480	0
69922	0
69925	0
69933	1	it became "Quoted" last month

Please help me.

[v-jianboli-msft]

Hi @timazarj ,

Please try:

Lead Become Quoted Within Last four months = 
VAR _a =
    SELECTCOLUMNS ( FILTER(ALL('Table'),[ClientId]=MAX('Table'[ClientId])), "Log", [ClientLog] )
VAR _b =
    CALCULATE (
        MAX ( 'Table'[LogDate] ),
        FILTER ( FILTER(ALL('Table'),[ClientId]=MAX('Table'[ClientId])), [ClientLog] = "Quoted" )
    )
RETURN
    IF ( "Quoted" IN _a && _b >= EDATE ( TODAY (), -4 ) && _b <= TODAY (), 1, 0 )

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-jianboli-msft · Accepted solution]

Hi @timazarj ,

Please try:

Lead Become Quoted Within Last four months =
VAR _a =
    SELECTCOLUMNS ( 'Table', "Log", [ClientLog] )
VAR _b =
    CALCULATE (
        MAX ( 'Table'[LogDate] ),
        FILTER ( 'Table', [ClientLog] = "Quoted" )
    )
RETURN
    IF ( "Quoted" IN _a && _b >= EDATE ( TODAY (), -4 ) && _b <= TODAY (), 1, 0 )

Output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[timazarj]

Thank you for the response. However, it doesn't work properly. 

in the model, you attached, if you add the log column to the table it only showed 1 when the log is "Quoted"

[timazarj]

Hi,

Thank you!

However, it must show 1 for all the clientID=69933 and 67206.

[Bifinity_75]

Hi @timazarj , try this calculate column:

Lead Become Quoted Within Last four months = if(Table1[ClientLog]="Quoted" && 
            Table1[LogDate]>=DATE(YEAR(TODAY()),MONTH(TODAY())-4,DAY(TODAY()))
            ,1,0
            )

The result:

Best regards