Average results over the last 4 records

[DRL]

Dear community, 

I have searched to solve this but did not find an answer that solves what I trying to achieve. 

We are capturing the result of chemical analysis on our samples. 

We use a table (in Excel currently) that averages the results over the last 4 samples, 3 days, week and month. 

I am struggling to find the way to create the average for the last 4 records. 

The table is as follow

Analysis type	Sampling date and time	Result 1 (%)	Result 2 (%)	Result 3 (%)
Analysis 1	02/22/2022 08:00:00	1	48	0.01
Analysis 2	02/22/2022 08:00:00	1.2	47	0.02
Analysis 1	02/22/2022 10:00:00	0.8	49	0.03
Analysis 2	02/22/2022 10:00:00	0.6	50	0.02
Analysis 1	02/22/2022 12:00:00	1.5	47	0.04
Analysis 2	02/22/2022 12:00:00	2.6	46	0.03
Analysis 1	02/22/2022 14:00:00	0.1	47.5	0.05
Analysis 2	02/22/2022 14:00:00	0.4	48	0.12

In this case, I'd like to have the following result for Analysis 1:

Sampling period	Result 1 (%)	Result 2 (%)	Result 3 (%)
Last 4 samples	0.85	47.875	0.13
Last 3 days	Av	Av	Av
Last week	Av	Av	Av
Last month	Av	Av	Av

Could you please help? 

Thank you so much for your time, 

[Daryl-Lynch-Bzy · Accepted solution]

Hi @DRL , I hope you find the following useful.

If I create the following DAX function, the result is 1.025 (for the table above).

Average Result 1 (All) = AVERAGE( table[Result 1] )

In your scenario you want separate Analysis 1 and Analysis 2 results, so you use the CALCULATE function to apply filter context.

Average Analysis 1 = CALCULATE ( AVERAGE( table[Result 1] ) 
                                   , table[Analysis type] = "Analysis 1" ) --0.85
Average Analysis 2 = CALCULATE ( AVERAGE( table[Result 1] ) 
                                   , table[Analysis type] = "Analysis 2" ) --1.2

Consider the scenario where the above is filtered to Date

Average Analysis 1 Last Month = CALCULATE ( AVERAGE( table[Result 1] ) 
                                   , table[Analysis type] = "Analysis 1" 
                                   , PREVIOUSMONTH( calendar[Date] ) ) 
Average Analysis 1 Last Week = CALCULATE ( AVERAGE( table[Result 1] ) 
                                   , table[Analysis type] = "Analysis 1" 
                                   , DATEADD( calendar[Start of Week] , -7 , DAY ) ) 

The last week calculation requires are Calendar table.  Refer here for instructions:

Week to Date Calculation in Power BI with DAX - RADACAD

To get the last 4 samples your "table" would need a Sequential Index (add using Power Query).  
Add an index column (Power Query) (microsoft.com)

With the index column, the following is possible:

Average Analysis 1 Last 4 Samples = 
  VAR _maxID = MAX ( table[ID])
  VAR _OffsetID = _maxID - 7  --This assumes that ROW ID is unique to each sample 1 / sample 2.  If the max is 8, then 8, 6, 4, 2 are included. 
RETURN
  CALCULATE ( AVERAGE( table[Result 1] ) 
              , table[Analysis type] = "Analysis 1" 
              , KEEPFILTERS ( table[ID] > _OffsetID ) )