Forum Discussion
Filters
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
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.
2 Replies
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.
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.
