Forum Discussion
Solved
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 a...
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.
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.