PowerBI Measure: Moving Average on Graph that responds to filters

[Anonymous]

I have a table, let's call table 1, has columns: ID, Completion Date, Completion Date Start of Week, Team, Type of Work - a small snapshot of it could look like the data below.

There are several teams, several types, and all teams can complete any task type.

The desired output, is a bar and line graph. x-axis = Completion Date Start of Week, Bar = Count of ID's in week, Line = 4 Week Moving Average of Count of ID's in Week. The Moving Average must respond to filters on teams and task type.

Note this is a large model with many linking tables and cannot use BI data heirarchies for whatever reason

_4WMA_IDs =

VAR MinDate = MIN('Table1'[Completion Date Start of Week])

RETURN

DIVIDE(

CALCULATE(

COUNT('Table1'[ID]),

FILTER(

ALL('Table1'[Completion Date Start of Week]),

AND([Completion Date Start of Week] >= MinDate - 21, [Completion Date Start of Week] <=MinDate))

        ),

        4)

There is one other table which links to this table, and has a filter applied at the page level. It doesn't seem to impact anything (and basically wouldn't filter anything out of the table from the last 3 years).

[AnalyticsWizard]

@Anonymous 

To create a bar and line graph in Power BI with the x-axis as ‘Completion Date Start of Week’, bars representing the count of IDs per week, and a line for the 4-week moving average of the count of IDs, you can follow these steps:

1. Create a Measure for Count of IDs:

CountOfIDs = COUNT('Table1'[ID])

1. Modify the 4-Week Moving Average Measure: Your current measure seems correct, but make sure that the column references are accurate and that the measure is responsive to slicers or filters applied to your report. Here’s a slightly modified version of your measure:

_4WMA_IDs =
VAR MinDate = MIN('Table1'[Completion Date Start of Week])
RETURN
DIVIDE(
    CALCULATE(
        [CountOfIDs],
        FILTER(
            ALL('Table1'),
            'Table1'[Completion Date Start of Week] >= MinDate - 21 &&
            'Table1'[Completion Date Start of Week] <= MinDate
        )
    ),
    4,
    BLANK()
)

1. Create the Visual:

   • Drag the ‘Completion Date Start of Week’ to the x-axis.
   • Drag the ‘CountOfIDs’ measure to the value field and set it as a bar chart.
   • Drag the ‘_4WMA_IDs’ measure to the value field and set it as a line chart.

2. Adjust Filters: Ensure that any page-level filters or slicers are not inadvertently filtering out data you want to include in your calculations.

3. Test the Visual: Apply different filters for teams and task types to ensure the 4-week moving average line responds accordingly.

If you’re still encountering issues with the measure not responding to filters, you may need to review the relationships between tables and ensure that the filter context is being passed correctly through those relationships. If necessary, you can use the USERELATIONSHIP function in DAX to specify which relationship to use in a particular calculation. Remember, DAX operates on tables and columns, not on individual cells or rows. Therefore, transformations that require cell-by-cell operations are often more complex in DAX than in Power Query. 

[Anonymous]

Hi, thanks for your response!

I was having some issues with the formatting of my question and apparently submitted only part of what I intended to.

Basically, the issue is the moving average is wrong when responding to filters, particularly when multiple teams and multiple work types are selected.

I've done some more troubleshooting and 90% sure I've figured out what's causing the problem, but don't know what the solution is. Suppose I am filtering for Team = Team 1 or Team 2, and Type = Type A or Type B.

Then if this is the total number of entries during the relevant 4 week period:

(Four week period total entries)	Type A	Type B
Team 1	20	10
Team 2	25	5

and this is the total number of entries for Start of Week = 25/03/24:

(Four week period total entries)	Type A	Type B
Team 1	4	2
Team 2	8	null (or effectively count = 0)

Then my moving average will miss the 5 entries completed by Team = 2 with Type = B in the 3 weeks I care about that aren't in the correct period (so will be 1.25 = 5 / 4 below what it should be).

[Anonymous]

Hi @Anonymous ，

How about use this measure?

_4WMA_IDs = 
VAR __MaxDate = CALCULATE(MAX ('Table1'[Completion Date Start of Week] ),ALLSELECTED('Table1'[Type of Work],'Table1'[Team]))
VAR __Numerator = 
        CALCULATE (
            COUNT ( 'Table1'[ID] ),
                AND (
                    'Table1'[Completion Date Start of Week] >= __MaxDate - 21,
                    'Table1'[Completion Date Start of Week] <= __MaxDate
                )
    )
VAR __Result = DIVIDE(__Numerator,4)
RETURN
    __Numerator

Best Regards,
Gao
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 -- China Power BI User Group

[Anonymous]

At least for me, when on the graph this is just returning the exact same original (incorrect) number I have been able to obtain, so looks like it's still not including them to me.