Filter data for Date 5 minutes ago

[Jimm]

I actually have some monitoring samples that I take each 5minutes, some of this values are cumulative and I need to take previous value in order to achieve: 

5 Min Diff Value = Actual Value - Previous Value

So far I was able to achieve the following...

LOAD_DATE_MINUS_5 = CALCULATE(MAX(LKP_Calendar[Load_Date])-(5/1440))

This is returning the previous 5 min date.

Batch_Requests_Delta = CALCULATE(SUM(DWH_ENGINE_KPIs[Batch_Requests_Cumulative]);FILTER(DWH_ENGINE_KPIs;DWH_ENGINE_KPIs[LOAD_DATE_MINUS_5])) 

This is basically returning wrong data, I think it's a total SUM of values, is not being filtered by previous date.

I know I'm missing something but I'm not sure what...

Any help will be appreciated 😄

[v-sihou-msft · Accepted solution]

@Jimm

I assume your [LOAD_DATE_MINUS_5] is a measure, and you have built the relationship properly between "DWH_ENGINE_KPIs" table and "LKP_Calendar" table. So you can just write your [Batch_Requests_Delta] like:

=
CALCULATE (
    SUM ( DWH_ENGINE_KPIs[Batch_Requests_Cumulative] ),
    FILTER ( LKP_Calendar, LKP_Calendar[Load_Date] = [LOAD_DATE_MINUS_5] )
)

 Regards,

[Anonymous]

Batch_Requests_Delta = CALCULATE(SUM(DWH_ENGINE_KPIs[Batch_Requests_Cumulative]);FILTER(DWH_ENGINE_KPIs;DWH_ENGINE_KPIs[LOAD_DATE_MINUS_5])) 

That looks a bit weird to me.  I would expect something more like:

Batch_Requests_Delta = CALCULATE(SUM(DWH_ENGINE_KPIs[Batch_Requests_Cumulative]);FILTER(ALL(DWH_ENGINE_KPIs); DWH_ENGINE_KPI[LOAD_DATE] = MAX(DWH_ENGINE_KPIs[LOAD_DATE_MINUS_5])))

Or... something.  🙂  Like we are trying to find the correct LOAD_DATE... the one that matches the MINUS_5 column, ya know!?