Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 😄
Solved! Go to Solution.
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,
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,
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!?