Need help with complicated metric

[dioskoro1]

Hi - 

Im at the end of my wits and i wanted to ask anyone can share their insights here.

Basically, we have a metric that aims to measure connection to customer. We have basically two levels: Ticket Level, Contact Level. What's in the photo below is the just showing contact level (meaning a contact can have multiple tickets associated to them).  The way we want to measure it is just counting just 1 valid connection to a contact, doesnt matter how many tickets are under him/her. 

So if contact 1 has 10 tickets, and we have made a connection to any of those 10 ticket, the numerator should be 1. (this is true even if we made all connection to 10 tickets, numerator should just be 1). This is straightforward and I manage to do it by using a distinctcount and calculcate/count then filter it. 

The problem is, we also want to put a limit to the validity of the call. i.e we have a contact that appears in Q1 2023 & Q1 2024 and we made a connection to him/her during Q1 2023. In my current workaround, the connection made in Q1 2023 is still valid for Q1 2024 but they wanted to put a 6 month limit to the validity of the connection.

What's the best way to go about it? Thanks!

[v-yaningy-msft · Accepted solution]

Hi, @dioskoro1 

According to your description, you want to realize filtering data according to time again. You should first define what the "current date" is that is used to help filter the data within six months, find the maximum date of each piece of data for all the matches, if the date of the piece of data is the maximum date, then display it after the piece of data, and the other rows with the same ID display the maximum date minus six months, then compare all the rows with the same ID. DATE of the rows with the date in the MAX Date column, if it is greater than the date in the MAX Date column, then that row of data is eligible. You can refer to the following screenshot to understand

MaxDateColumn = 
VAR _currentID = 'Table'[customer_contact_contact_id]
VAR _maxDateForID =
    CALCULATE (
        MAX ( 'Table'[Created Date] ),
        FILTER ( 'Table', 'Table'[customer_contact_contact_id] = _currentID )
    )
VAR _sixMonthsAgo =
    EDATE ( _maxDateForID, -6 )
RETURN
    IF ( 'Table'[Created Date] = _maxDateForID, _maxDateForID, _sixMonthsAgo )

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum