average of last values for same day of the week

[Anonymous]

Please can some one help. I'm new to power BI and have spent datys watching videos from Enterprise DNA and Mitchell Pearson but just cannot seem to hook this measure up. 

I have a table named "Production Stats) with two columns.

(Column 1) "Date" which is weekdays only so no Sat & Sun

(column 2) "Daily totals".

I pull both date and daily total values from an excel spreadsheet these update into power Bi as the excel spreadsheet is updated. 

My main date table has days of wek defined. 

If today's day of the week is Monday I want to show the average of the last 4 mondays from my daily totals (not including today). I'd like this to dynamically update as we pass through the week, so tomorrow will automatically show the last 4 Tuesdays (not including today) I'd like to display this in a card. 

I'd be so happy if someone could solve this for me. 

[Anonymous]

@Greg_Deckler Ignore my last commet, I fully retyped your measure out and it's worked. you are an absolute star. thank you. 

Are you aware of anyway that I can lookup exactly which values the measure is using. I only ask becuase when I average the last 5 weeks of data for Tuedays (todays day) the value in Power Bi doesn't match the value in excel. 

[Anonymous]

@Greg_Deckler I really appreciate you taking the time to respond. thank you. everything seems to be accepted in DAX apart from the very last part. It won't accept that I want to input "Daily totals", instead the Intellisense is automatically referencing my "date" table. 

I've attached two pics that may help? 

[Greg_Deckler]

@Anonymous Use your fact table for filtering. Also, I updated the definition because I forgot an ALL/ALLSELECTED.

[Anonymous]

@Greg_Deckler Again, thank you for your time but I'm clearly lacking the understanding to make your DAX work. I just cannot figure it out. I guess I'm too new to power BI to fully understand it yet. 

I can't figure out how to correct the error message. 

[Greg_Deckler · Accepted solution]

@Anonymous Maybe:

Measure = 
  VAR __Date = MAX('Table'[Date])
  VAR __Weekday = WEEKDAY(__Date)
  VAR __Table = FILTER(ALLSELECTED('Table'),[Date] < __Date && [Date] >= (__Date - 5*5))
RETURN
  AVERAGEX(FILTER(__Table, WEEKDAY([Date]) = __Weekday),[Daily totals])

[Anonymous]

@Greg_Deckler Is there any chance you could help me out a little bit further with this one please? 

I've made two changes. 

1) extended my date table which now goes up to Nov 2024

2) data refresh from sources. 

Since doing so the card is now returning "(Blank)" I think this is because the dax you kindly wrote for me doesn't look for todays date before completing the rest of the calculations. In my date table I do have a calculated column that returns the value "Yes" to show Is today. 

I'm sorry if I am wrong. but if I am correct would you be able to ammend the measure to look for today. 

past 5 weekly average output per hr = 
  VAR __Date = MAX('Date'[Date])
  VAR __Weekday = WEEKDAY(__Date)
  VAR __Table = FILTER(ALLSELECTED('Production stats'), [Date] < __Date && [Date] >= (__Date - 5*5))
Return  
    AVERAGEX(FILTER(__Table, WEEKDAY([Date]) = __Weekday), [Daily total Packs Per Hr (Line 2) ])