power bi, calculate average over specific weekdays in the last month

Question

I have the following data:

My goal is to create the following measure:

For a selected day (which the user will select), calculate the average of 4 previous rates on the same week day (in other words, look at 4 weeks, take the rate of each day where the week day is the same as the selected and calculate the average over the 4). The 4 days do not include the selected day

I first did the following, which turned out to be wrong:

Measure 1:

Success Count 4 previous weeks =

var selectedwwekDay = SELECTEDVALUE(Transactions[Weekday])

var selectedDate = SELECTEDVALUE(Transactions[Date])

return

CALCULATE(COUNTROWS(Transactions), Transactions[Result ] = "Success", Transactions[Weekday] = selectedwwekDay, Transactions[Date] < selectedDate, Transactions[Date] > selectedDate - 30)

Measure 2:

Count 4 previous weeks =

var selectedwwekDay = SELECTEDVALUE(Transactions[Weekday])

var selectedDate = SELECTEDVALUE(Transactions[Date])

return

CALCULATE(COUNTROWS(Transactions), Transactions[Weekday] = selectedwwekDay, Transactions[Date] < selectedDate, Transactions[Date] > selectedDate - 30)

Mearue 3 to calculate the average:

Last 4 weeks average =

DIVIDE( aMeasures[Success Count 4 previous weeks], [Count 4 previous weeks])

Problem with this measure is that it takes weighted average. If I had more transactions on a specific day, the average will be impacted, while I don't want it.

Example:

If user choses date 3/20/2022, the relevant data is:

My measure is basically calculating the average over all 4 weeks, regardless of the rate per day:

5 Success/ 6 Total = 83%

But I am interested in taking the average of rate over 4 days:

Average (100%, 100%, 100%, 50%) = 87.5%

How would I do it?

I think I need to create a separate table that will hold one record per day with the success rate, but not sure how to do that.

Thanks!