Filters

[powerbiuser9999]

Hi all

I hope someone can help me.

I am calculating an average of a column, and need it to calculate the average for 28 days prior to the MAX value in another column.

When writing my DAX i am unable to overcome the MAX value adhering to the filter context, when I would like it to ignore it.

So as an example.

DATE(uk)       ITEM        NUMBER

21/4/22         W            10

12/1/22         E             10
14/11/21       W            20
7/5/22           S             10

The MAX value is '7/5/22' but when the filter context is changed to select 'W' in a slicer, it defaults to '21/4/22', which then throws out the 28 day calculation

Im working with this so far:
CALCULATE(AVERAGE(NUMBER),DATESINPERIOD(DATE,MAX(DATE),-28,DAY))

which works fine, aside from the issue above.

Can anyone assist?

Many thanks

[v-xiaotang · Accepted solution]

Hi @powerbiuser9999 

The reason for this problem is that slicer is using ITEM from the fact table, you need to create another table containing ITEM and use it for slicer.

I mean, when you select W from the table below, then it will filter the table where ITEM=W and then return the max date where  ITEM=W. 

Best Regards,

Community Support Team _Tang

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

[AlexisOlson]

Try calculating the max date as a variable where you use ALL to remove the item filter context from your slicer.

For example, your measure might look something like this:

28 Day Avg =
VAR _MaxDate = CALCULATE ( MAX ( Dates[Date] ), ALL ( Table1 ) )
RETURN
    CALCULATE (
        AVERAGE ( Table1[Number] ),
        DATESINPERIOD ( Dates[Date], _MaxDate, -28, DAY )
    )

I don't know what tables and relationships you have though, so you'll need to modify this according to your specific situation.