Average of last 3 dates per member ID

[Anonymous]

Hey guys,

The table on the left is fact table. 

The table on the right summarizes the Sum of TWIN by Member ID. 

1. How do I create a measure / calculated column to calculate average TWin per day  by Member ID.

80680001851 will return  1,180 divide by 6 distinct dates = 196.66 per day

80680003100 will return 410 divide by 4 distinct dates = 102.5 per day

2. How do I repeat the same thing, but limit to only last 3 (Descending Order) distinct dates by Member ID.

80680001851 will return (31 Dec) 30 + (29 Dec) 200 + (27 Dec) 400 = 630 then divide by 3 = 210

80680003100 will return (28 Dec) 30 + (25 Dec) 10 + 190 + (20 Dec) 20 + 80 then divide by 3 = 110

Download here

[LivioLanzo · Accepted solution]

Hi @Anonymous

try these measures:

AVG PEr Day = 
AVERAGEX(
    SUMMARIZE( data, data[Member ID], data[Trading Date] ),
    CALCULATE( SUM( data[TWin] ) )
)

Measure = 
AVERAGEX(
    TOPN(
        3, SUMMARIZE( data, data[Trading Date], data[Member ID] ), [Trading Date], DESC
    ),
    CALCULATE( SUM( data[TWin] ) )
)

[Anonymous]

@LivioLanzo u r brilliant!