excluding values
1 Topic
Excluding Monthly Violations in Store Complaints Rolling Average DAX Calculation
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 YesSolved634Views0likes1Comment