Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Laila92
Helper V
Helper V

Count of time period breach over time

I will start with some background on this one. On each customer, the customer support has so called 'check-in calls'. Customer support (cs) is supposed to check in with each client each 60 days once the client is won. If this goes over 60 days, it is a breach.

Now I want to see over time, the % of breaches out of total clients.  

I think I have to make a virtual table going over each day and each client, checking if there is a breach, but I have not managed yet.

I am working with two tables here, a dates table and a sales table.


This measure shows the most recent check in before the date that we are looking at

Days since last DX Checkin 2 =
VAR last_dx_call =
    CALCULATE (
        MAX ( Deals[Activities.Due date] ),
        FILTER ( Deals, Deals[Activities.Type] = "DX Check-In Call" ),
        FILTER (
            Deals,
            Deals[Activities.Due date] <= CALCULATE ( MIN ( Dates[Date] ) )
        )
    )
VAR LAST_CALL_WITH_WON =
    IF (
        AND ( last_dx_call = BLANK (), MIN ( Dates[Date] ) >= [Won Date] ),
        [Won Date],
        last_dx_call
    )
VAR blll =
    IF (
        AND (
            CALCULATE (
                MAX ( Deals[Activities.Due date] ),
                FILTER ( Deals, Deals[Activities.Type] = "DX Check-In Call" ),
                FILTER (
                    Deals,
                    Deals[Activities.Due date] <= CALCULATE ( MIN ( Dates[Date] ) )
                )
            )
                = BLANK (),
            MIN ( Dates[Date] ) >= [Won Date]
        ),
        [Won Date],
        CALCULATE (
            MAX ( Deals[Activities.Due date] ),
            FILTER ( Deals, Deals[Activities.Type] = "DX Check-In Call" ),
            FILTER (
                Deals,
                Deals[Activities.Due date] <= CALCULATE ( MIN ( Dates[Date] ) )
            )
        )
    )
RETURN
    LAST_CALL_WITH_WON

So this one works.

Then, I can also check with a datediff measure whether the number above is more than 60 days, and return a 1 or a 0 for example. 

However, if I then want to aggregate the NUMBER of clients it does not work anymore.

 

How can I make a virtual table that goes over each day and client and checks if they, on a specific day, are a breach?

I added data with the customer ids, and check in date here: https://docs.google.com/spreadsheets/d/1oHq_g3HQcXB9ThbdDeNDyeyAU-Hf7kUxfEVhXkAp0to/edit?usp=sharing

 

7 REPLIES 7
v-yalanwu-msft
Community Support
Community Support

Hi, @Laila92 ;

The file you shared cannot be opened. Can you share it again? Or after sharing screenshots to remove sensitive information?

The most important parts are:
1. Sample data as text or screenshots.
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

lbendlin
Super User
Super User

For each customer use ADDCOLUMNS to create and manipulate a table variable that holds the dimension (customer) and facts (call dates) and then computes the interval between calls, then returns a count of rows where the interval is bigger than the limit. 

 

Note that this process is blind to the interval until the first listed call, and the interval after the last listed call.

thank you @lbendlin , i like that approach. however, how will i relate it to my calendar table, to see how many customers on each day did not have a call for > 60 days?

You will get that automatically when you put the calendar date and the measure into a table visual.

 

If you like more help please provide sample data in usable format (not as a picture - maybe insert into a table?).

Thanks, I had added data with a google sheets link. 
Does that help?

i am just not sure how the table would help me with the over time axis

First step is to import your sample data into Power BI. Next, we add a calendar table. You can ignore that step if you already have one

 

 

 

Dates = CALENDARAUTO(12)

 

 

 

In your case this will create a table with all dates for 2019 through 2021.  We also mark the table as a Date table, just because.

lbendlin_0-1629644783259.png

 

Next step is to add the measure(*) calculation for each date in the Dates table.  However, as I mentioned earlier you will have blind spots at the beginning of the date range.  So let's set a rule that a customer must have had at least one call prior to the date before being considered for the interval breach.  This makes the logic rather simple:

 

For each date in the Dates table find the difference between the last call for a given customer and the date, and mark it in case it exceeds 60.  Do this for all customers, and sum up the result.

 

* You could argue that a measure is not required as the data (call event) is final and not influenced by any user interactions with filters.  So a calculated column will work equally well - unless you have an argument against that?

 

Here's the next step using a calculated column.  Collect all customer ID, and grab their latest call data

 

lbendlin_1-1629646714364.png

 

(This actually raises an interesting question - Do you assume all customers to be active all the time, or do you have contract start and end dates?)

 

Next - check if the difference is greater than 60 

 

lbendlin_2-1629647154635.png

 

And lastly - return the ratio.

 

lbendlin_3-1629647371163.png

 

(note that I picked the start of each calendar month, but you can pick any day you like)

 

Exceeded = 
var d=[Date]
var a=ADDCOLUMNS(VALUES(CheckIn[Customer ID]),"LastDate",CALCULATE(max(CheckIn[Check in date]),CheckIn[Check in date]<d))
var b=ADDCOLUMNS(a,"Difference",d-COALESCE([LastDate],d))
var c=ADDCOLUMNS(b,"Exc",if([Difference]>60,1,0))
return DIVIDE(SUMX(c,[Exc]),COUNTROWS(c),0)

Thank you for the explanation!

so i actually already have this with their latest calls. However, my challenge is showing it over time for each customer. Lets consider customer A, who joined december 2020. He had a checkin call july 1st 2021, so is now not breach. However, his previous check in call was on march 1st 2021, so during  may and june 2021, this customer was on a breach because he had no check in call for more than 60 days since may 1st (or lets just say two months). Before that, he had a checkin call january 1st 2021, so he was not breach before that. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.