Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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 |
Solved! Go to 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.
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.
User | Count |
---|---|
20 | |
20 | |
14 | |
10 | |
8 |
User | Count |
---|---|
29 | |
28 | |
13 | |
12 | |
11 |