Calculate Average value for same weekday all history

[fgenteur]

Hi All,

Hope you guys can help me with this one. I found several topics that had similar requests but none was close enough to help me figure it out.

So I have timestamped daily values for the past 2 years and I want to create a baseline activity so here's what I'm trying to do:

On each Monday, average out all previous Mondays

On each Tuesday, average out all previous Tuesdays

and so on...

What would be the DAX formula for such measure or calculated column?

Thanks a lot!

[fgenteur]

Thanks! What about if I need to do the same per item like:

date1 | item1 | value

date2 | item1 | value

date1 | item2 | value

...

[amitchandak · Accepted solution]

@fgenteur , better to have weekday column in date table or table

Weekday = weekday([Date])

they try a measure like
calculate(Average(Table[Value]), filter(Allselected('Date'), 'Date'[Weekday] =max('Date'[Weekday])))

or

calculate(Average(Table[Value]), filter(Allselected('Date'), weekday([Date]) =maxX('Date',weekday([Date]))))