Excluding Monthly Violations in Store Complaints Rolling Average DAX Calculation

[bbui11]

Hi,

I have a requirement to create a limit value that determines whether the # of store complaints for a given month is considered a violation. The limit is calculated as a rolling average of prior months, but must exclude prior months that were violations.

As an example, the table below shows the store complaints for each month. The "Store Rolling Average Complaints Excluding Violations" value is what I am trying to get, but I run into a circular dependency error when attempting to exclude violations.

Store Complaints = # of complaints for the month

Store Rolling Average Complaints = The rolling average value that includes prior violation months. Just included it as a reference, since this rolling average calc is straightforward. 

Store Rolling Average Complaints Excluding Violations = The desired DAX measure, that calculates a rolling average value, but exclude prior violations.

Violations = Violation indicator for each month, and is marked as "Yes" when Store Complaints > Store Rolling Average Complaints Excluding Violations

*This needs to be a DAX measure because there are various filters that must be applied to the matrix

Please let me know if any addtl. info is needed. TIA.

	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Store Complaints	6	3	8	2	11	5	12	8	6	8	1	5
Store Rolling Average Complaints		6.00	4.50	5.67	4.75	6.00	5.83	6.71	6.88	6.78	6.90	6.36
Store Rolling Average Complaints Excluding Violations		6	4.5	4.5	3.66	3.66	3.66	3.66	3.66	3.66	3.66	3
Violations	No	No	Yes	No	Yes	Yes	Yes	Yes	Yes	Yes	No	Yes

[v-rzhou-msft · Accepted solution]

Hi @bbui11 ,

As far as I know, Dax doesn't support us to do looping.

I think you may try looping in Power Query. Here I have a test,  I hope it could help you.

let
    Source = Table.FromRecords({
        [Date=#date(2024, 1, 1), Complaints=6],
        [Date=#date(2024, 2, 1), Complaints=3],
        [Date=#date(2024, 3, 1), Complaints=8],
        [Date=#date(2024, 4, 1), Complaints=2],
        [Date=#date(2024, 5, 1), Complaints=11],
        [Date=#date(2024, 6, 1), Complaints=5],
        [Date=#date(2024, 7, 1), Complaints=12],
        [Date=#date(2024, 8, 1), Complaints=8],
        [Date=#date(2024, 9, 1), Complaints=6],
        [Date=#date(2024, 10, 1), Complaints=8],
        [Date=#date(2024, 11, 1), Complaints=1],
        [Date=#date(2024, 12, 1), Complaints=5]
    }),

    CalculateRollingAverage = (ComplaintsList as list, Index as number) as number =>
        let
            PreviousComplaints = List.FirstN(ComplaintsList, Index-1),
            ValidComplaints = List.Select(PreviousComplaints, each _ <= List.Average(PreviousComplaints)),
            RollingAverage = if List.Count(ValidComplaints) = 0 then 0 else List.Average(ValidComplaints)
        in
            RollingAverage
,
    AddRollingAverage = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    AddRollingAverageExcludingViolations = Table.AddColumn(AddRollingAverage, "Rolling Average Excluding Violations", each CalculateRollingAverage(AddRollingAverage[Complaints], [Index])),
    #"Changed Type" = Table.TransformColumnTypes(AddRollingAverageExcludingViolations,{{"Rolling Average Excluding Violations", Currency.Type}}),
    AddViolations = Table.AddColumn(#"Changed Type", "Violations", each if[Index] = 1 then "No" else if [Complaints] > [Rolling Average Excluding Violations] then "Yes" else "No"),
    #"Removed Columns" = Table.RemoveColumns(AddViolations,{"Index"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Rolling Average Excluding Violations", Int64.Type}})
in
    #"Changed Type1"

Result is as below.

Best Regards,
Rico Zhou

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