Forum Discussion

jgeddes's avatar
jgeddes
Super User
5 months ago

Maybe something like this will work for you...
Create a calculated column

Rolling 4 Hours Average = 
var _fourHours = 
[DateTime] - (1/24*4)
var _currTime = 
[DateTime]
var _result = 
SUMX(FILTER('Table', 'Table'[LPAR] = [LPAR] && ('Table'[DateTime] <= _currTime) && 'Table'[DateTime] >= _fourHours), [MIPS])/4
RETURN
_result

 

Create measure

Max MIPS = 
var _max = 
SUMMARIZE(
    ALLSELECTED('Table'),
    'Table'[DateTime].[Year],
    'Table'[DateTime].[Month],
    "__max", DIVIDE(MAX('Table'[MIPS]), MAX('Table'[Rolling 4 Hours Average]))
)
RETURN
MAXX(_max, [__max])

 

It may not be the exact answer, but it should get you pointed in the right direction.

  • DineshArivu's avatar
    DineshArivu
    Helper I
    4 months ago

    jgeddes Thanks for your solution. Unfortunately your rolling avg DAX taking very long time to create and not finished. It may be related to huge rows.

    I have already created a measure for this and it looks OK when I used to add with few columns as below :

    1st 3 rows blank as we don't have strictly 4 rows to calculate 4hrs rolling, from the 4th row the calculation starts and giving the expected value as a result.

     

    but the same measure is not working as expected when it applies to original table (More fields) as below :

     

    DAX i used :

    Rolling 4h Avg__ =
     VAR RelationDistinct = SUMMARIZE ( ALLSELECTED ( 'VW_MF_TRANSACTIONS' ), 'VW_MF_TRANSACTIONS'[LPAR], 'VW_MF_TRANSACTIONS'[SERIAL], 'VW_MF_TRANSACTIONS'[WDID],'VW_MF_TRANSACTIONS'[CLIENT_NAME],'VW_MF_TRANSACTIONS'[MetricDateTime_1] )
     VAR W = WINDOW ( -3, 0, RelationDistinct, ORDERBY ( 'VW_MF_TRANSACTIONS'[MetricDateTime_1], ASC ), PARTITIONBY ( 'VW_MF_TRANSACTIONS'[LPAR], 'VW_MF_TRANSACTIONS'[SERIAL],'VW_MF_TRANSACTIONS'[WDID], 'VW_MF_TRANSACTIONS'[CLIENT_NAME]) )
     VAR Rows4 = COUNTROWS ( W ) VAR Sum4 = SUMX ( W, [MAX MIPS] )
    RETURN IF ( Rows4 = 4, DIVIDE ( Sum4, 4 ), BLANK() )
     
    Please help to sort this asap .

    Thanks
    DK