Getting average for x number of most recent days that contain values

[Anonymous]

Hello,

I have source data with dates, member ID's and values for a metric. I would like to get an average for each member of the metric for the last 3 dates in the record that contain any values. Not every date entry has a metric value and for a given member there could be gaps between dates in the record. Also for a given date there might be multiple entries for a member but not all will necessarily have a metric value. From the table below the latest 3 days with values for member A are 4-12-21, 4-11-21 and 4-9-21. The average of the metric for member A would be (100+90+100+110+130+200)/6 = 121.7. Is there a way to do this?

Date	Member	Metric
4/9/21	A	100
4/9/21	A
4/9/21	A	90
4/9/21	A	100
4/10/21	B	150
4/10/21	A
4/10/21	B	120
4/11/21	B	140
4/11/21	A	110
4/11/21	A
4/11/21	B	160
4/11/21	A	130
4/12/21	A
4/12/21	B	180
4/12/21	B
4/12/21	A	200
4/12/21	A
4/13/21	A
4/13/21	B

Thank you,

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Please check the link down below and the measure, whether it is what you are looking for.

https://www.dropbox.com/s/fj1nhttvpr8vd1u/sford.pbix?dl=0 

Latest 3 days avg =
VAR currentmembers =
MAX ( Data[Member] )
VAR lastnonblankdate =
CALCULATE (
MAX ( Data[Date] ),
FILTER (
ALLSELECTED ( Data ),
Data[Member] = currentmembers
&& CALCULATE ( SUM ( Data[Metric ] ) ) > 0
)
)
VAR beforelastnonblankdate =
CALCULATE (
MAX ( Data[Date] ),
FILTER (
ALLSELECTED ( Data ),
Data[Member] = currentmembers
&& CALCULATE ( SUM ( Data[Metric ] ) ) > 0
&& Data[Date] < lastnonblankdate
)
)
VAR beforebeforelastnonblankdate =
CALCULATE (
MAX ( Data[Date] ),
FILTER (
ALLSELECTED ( Data ),
Data[Member] = currentmembers
&& CALCULATE ( SUM ( Data[Metric ] ) ) > 0
&& Data[Date] < beforelastnonblankdate
)
)
VAR datesfilter =
CALCULATETABLE (
FILTER ( ALLSELECTED ( Data ), Data[Member] = currentmembers ),
Data[Date] = lastnonblankdate
|| Data[Date] = beforelastnonblankdate
|| Data[Date] = beforebeforelastnonblankdate
)
RETURN
DIVIDE (
CALCULATE ( SUM ( Data[Metric ] ), datesfilter ),
CALCULATE ( COUNTROWS ( Data ), datesfilter )
- CALCULATE ( COUNTBLANK ( Data[Metric ] ), datesfilter )
)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/

[Anonymous]

Works perfectly. Thank you.

[amitchandak]

@Anonymous , Try a measure like

measure =
var _max = maxx(allselected(Table), Table[Date])
return
calculate(divide(sum(Table[Metric]),count(Table[Metric])), filter(allselected(Table), table[Member] = max(table[Member]) && Table[date] <=_max && Table[Date] >=_max -3))

or

measure =
var _max = maxx(allselected(Table), Table[Date])
return
calculate(divide(sum(Table[Metric]),count(Table[Metric])), filter(all(Table), table[Member] = max(table[Member]) && Table[date] <=_max && Table[Date] >=_max -3))