Identifying more than one item and newly added in a rolling 6 month period

[StevenHarrison]

Hi, we have a Complaints/Service request process set up in Dynamics 365 and I have been asked the following:

Do you think there could be a way of getting BI to send an email to me, as and when a service request or complaint is raised for the same property address in a 6 month rolling period?

I am looking to contact anyone that has had to raise a complaint or service request more than once, so an automated email to me with their address on is more than enough and I can then contact them from there?

Example data in a table below:

so please can you help me in how I would be able to high light in the rolling 6 month period if an address has more than one complaint/Service request and when new ones are added?

I have a matrix that shows roughly what is does.

createdon	CaseTitle	StatusName	C_TypeName	StatusCodeName	DepartmentNameNS	FeedbackCategory	FeedbackSubcategory	Tenant_Id_Name	Address
25/09/2024 00:00	COMP-2109	Closed	Complaint	Active	Property Services	Staff	Attitude /Behaviour – Staff	Joe Smith	57 Example Road;Town Name;;; Post Code
28/08/2024 00:00	COMP-2053	Closed	Service Request	Active	Property Services	Contractor A	Condition of Property	Joe Smith	57 Example Road;Town Name;;; Post Code
13/08/2024 00:00	COMP-2032	Closed	Service Request	Active	Property Services	Contractor A	Process	Joe Smith	57 Example Road;Town Name;;; Post Code
02/08/2024 00:00	COMP-2014	Closed	Service Request	Active	Property Services	Contractor B	Quality of works	Joe Smith	57 Example Road;Town Name;;; Post Code
24/05/2024 00:00	COMP-1874	Closed	Service Request	Active	Property Services	Local - Process / Service	Time to complete	Joe Smith	57 Example Road;Town Name;;; Post Code
12/02/2024 00:00	COMP-1599	Closed	Service Request	Active	Property Services	Contractor A	Time to complete	Joe Smith	57 Example Road;Town Name;;; Post Code
27/10/2023 00:00	COMP-1337	Closed	Service Request	Active	Property Services	Other	Communication	Joe Smith	57 Example Road;Town Name;;; Post Code

[Anonymous]

Hi All,
Firstly  mh2587 thank you for your solution!
And @StevenHarrison ,Based on your question, I'm going to break it down and realize it for you. The first one is a dynamic review of data over a six-month period:

Rolling6Months = 
DATEDIFF(
    MAX('Table'[createdon]),
    TODAY(),
    MONTH
) <= 6

Then each address is counted to see if any of the addresses exceeds 1 or keeps incrementing.

ComplaintsCount = 
VAR CurrentCount = CALCULATE(
    COUNT('Table'[Address]), 
    FILTER(
        'Table',
        'Table'[Address] = MAX('Table'[Address]) && 
        'Table'[Rolling6Months] = TRUE && 
        'Table'[createdon] = MAX('Table'[createdon]) 
    )
)
VAR CumulativeCount = SUMX(
    FILTER(
        ALL('Table'),
        'Table'[Address] = MAX('Table'[Address]) && 
        'Table'[Rolling6Months] = TRUE && 
        'Table'[createdon] <= MAX('Table'[createdon])
    ),
    CurrentCount
)
RETURN
CumulativeCount

Finally, you can set up alerts to see which addresses are receiving too many complaints.

IncrementCheck = 
IF(
    [ComplaintsCount] > 1,
    IF(
        [ComplaintsCount] > CALCULATE([ComplaintsCount], PREVIOUSDAY('Table'[createdon])),
        1,
        0
    ),
    0 
)

If you still have questions you can check my pbix file, I hope my solution will help you, I would be honored if my solution can solve your problem!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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

[StevenHarrison]

Hi @Anonymous , thank you very much for your reply, I will take a look today and see if I can implement the solution, just for clarity can you tell me which are new calculated columns and which are just measures?

Once again thank you @mh2587 and @Anonymous , I will definitely be working on this today at some point.

[Anonymous]

Hi @StevenHarrison ,

The DAX I'm using is all MEASURES, you just need to use the ones I've shown, have you tried any of the solutions that have been given so far, and if the problem has been solved, can you mark the post as solved? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
Looking forward to your reply!

[StevenHarrison]

Hi @Anonymous  - I am struggling somewhat with the following measure, having amended the code to use date time instead of date (as there can be more that one complaint/service request in a day):

ComplaintAlert =
VAR TodayCount =
CALCULATE (
COUNT ( 'Complaint (rk_complaint)'[CaseTitle] ),
FILTER ( 'Complaint (rk_complaint)', 'Complaint (rk_complaint)'[c_Date-TimeReceived] = NOW() )
)

VAR YesterdayCount =
CALCULATE (
COUNT ( 'Complaint (rk_complaint)'[CaseTitle] ),
FILTER ( 'Complaint (rk_complaint)', 'Complaint (rk_complaint)'[c_Date-TimeReceived] = NOW() - 1 )
)

VAR IsWithin6Months =
CALCULATE (
COUNTROWS ( 'Complaint (rk_complaint)' ),
FILTER (
'Complaint (rk_complaint)',
DATEDIFF ( 'Complaint (rk_complaint)'[c_Date-TimeReceived], NOW(), MONTH ) <= 6
)
) > 0

RETURN
IF (
IsWithin6Months && TodayCount > 1 && TodayCount > YesterdayCount,
"Alert: Increase Detected",
"No Change"
)

Alert: Increase Detected doesn't seem to pick out the increase in complaints/service requests, so image below:  any advice welcome

[Anonymous · Accepted solution]

Hi @StevenHarrison ,
I see that you are using the dax process tiggeralter, is successfully displayed, if you want to discuss another answer, then I tried to rewrite another dax, but the basic idea is still almost the same as my own, we will change the original code in the today function to achieve the same needs as my code.

ComplaintAlert = 
VAR CurrentRowDate =
    CALCULATE(
        MAX('Table'[createdon]),
        ALL('Table')
    )

VAR CurrentRowCount =
    CALCULATE(
        COUNTROWS('Table'),
        FILTER(
            'Table',
            DATEDIFF('Table'[createdon], CurrentRowDate, MONTH) <= 6
        )
    )

VAR PreviousRowDate =
    CALCULATE(
        MAX('Table'[createdon]),
        FILTER(
            ALL('Table'),
            'Table'[createdon] < CurrentRowDate &&
            DATEDIFF('Table'[createdon], CurrentRowDate, MONTH) <= 6
        )
    )

VAR PreviousRowCount =
    CALCULATE(
        COUNTROWS('Table'),
        FILTER(
            ALL('Table'),
            'Table'[createdon] = PreviousRowDate &&
            DATEDIFF('Table'[createdon], CurrentRowDate, MONTH) <= 6
        )
    )

VAR Cumulative =
    SUMX(
        FILTER(
            ALL('Table'),
            'Table'[Address] = MAX('Table'[Address]) &&
            'Table'[Rolling6Months] = TRUE &&
            'Table'[createdon] <=MAX('Table'[createdon])
        ),
        CurrentRowCount
    )

VAR Rolling6MonthsCheck =
    CALCULATE(
        COUNTROWS('Table'),
        FILTER(
            'Table',
            DATEDIFF('Table'[createdon], CurrentRowDate, MONTH) <= 6
        )
    ) > 0

VAR IncrementCheck =
    IF(
        Cumulative > 1,
        IF(
            Cumulative > PreviousRowCount,
            1,
            0
        ),
        0
    )

RETURN
IF(
    Rolling6MonthsCheck && IncrementCheck = 1,
    "Alert",
    "No Change"

I hope this code can help you answer your questions, this code can also be able to achieve the same effect, similar to my scattered dax organized together, if I can solve your problem I will be honored!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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

[StevenHarrison]

Hi @Anonymous , thank you for that, I will look to implement today and see if I can get the code to work.

Once again many thanks for your help.

[mh2587]

//Try this and adjust with your behalf might help you
ComplaintAlert = 
VAR TodayCount = 
    CALCULATE (
        COUNT ( 'Table'[CaseTitle] ),
        FILTER ( 'Table', 'Table'[createdon] = TODAY() )
    )

VAR YesterdayCount = 
    CALCULATE (
        COUNT ( 'Table'[CaseTitle] ),
        FILTER ( 'Table', 'Table'[createdon] = TODAY() - 1 )
    )

VAR IsWithin6Months = 
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            'Table',
            DATEDIFF ( 'Table'[createdon], TODAY(), MONTH ) <= 6
        )
    ) > 0

RETURN
IF (
    IsWithin6Months && TodayCount > 1 && TodayCount > YesterdayCount,
    "Alert: Increase Detected",
    "No Change"
)

[mh2587]

You can set up DataActivator Alert when the complaint count > 1 you can send alert notification.
Or use PowerAutomate

[StevenHarrison]

Thanks @mh2587 

I suppose the logic I'm trying to implement is:

1. what is the complaint count yesterday per location.
2. what is the complaint count today per location.
3. if it is the same do nothing.
4. if it is now greater than 1 or has increased from the last count (if more than 1 already) then send an email or create some sort of alert.
5. repeat daily looking back at a rolling 6 month cycle of data.