calculating averages of specific days of week

[Anonymous]

Not sure if this should go to DAX or Power Query forum (or if the solution is a combination of both..)

I have the following data:

Date	Tansaction ID	Result	Week Day
2/15/2022	1160	Success	3
2/15/2022	1238	Failed	3
2/16/2022	1361	Success	4
2/17/2022	1616	Success	5
2/18/2022	1667	Success	6
2/19/2022	1958	Success	7

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:

Date	Failed	Success	Total	Success Rate
2/20/2022		1	1	100%
2/27/2022		1	1	100%
3/6/2022		2	2	100%
3/13/2022	1	1	2	50%
3/20/2022	2		2	0%

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!

[Anonymous]

Thanks for the response.

FILTER function receives 2 arguments. What did you mean by:

Filter(all(Date) ,Date[Weekday] = selectedwwekDay, Date[Date] < selectedDate, Transactions[Date] >= Date - 28)

[amitchandak]

@Anonymous , Use a date table, you need to use all to get past dates

example

Success Count 4 previous weeks =
var selectedwwekDay = SELECTEDVALUE(Date[Weekday])
var selectedDate = SELECTEDVALUE(Date[Date])
return
CALCULATE(COUNTROWS(Transactions), Filter(Transactions, Transactions[Result ] = "Success") , Filter(all(Date) ,Date[Weekday] = selectedwwekDay, Date[Date] < selectedDate, Transactions[Date] >= Date - 28)

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4