Rolling 4 hours average

[DineshArivu]

Hi Experts,

Add one more column "Rolling 4 hours Average", it has to map current row MIPS value + previous 3 , then divided by 4.
we have a slicers to filter by region, by month, by date.

after this "Rolling 4 hours Average" created, we have to calculate MAX MIPS value without 4 hours rolling by Month and MAX MIPS value with 4 hours rolling by month.

I have tried the below DAX by result is not as expected.

Rolling 4h Avg (strict ÷ 4) =

VAR RelationDistinct =

    SUMMARIZE (

        ALLSELECTED ( 'VW_MF_TRANSACTIONS' ),

        'VW_MF_TRANSACTIONS'[LPAR],

        'VW_MF_TRANSACTIONS'[MetricDateTime_1]

    )

VAR W =

    WINDOW (

        -3, 0,

        RelationDistinct,

        ORDERBY ( 'VW_MF_TRANSACTIONS'[MetricDateTime_1], ASC ),

        PARTITIONBY ( 'VW_MF_TRANSACTIONS'[LPAR] )

    )

VAR Sum4 = SUMX ( W, [MAX MIPS])

RETURN DIVIDE ( Sum4, 4 )

Please help to fix this asap.

Thanks DK

[Anonymous]

Hi @DineshArivu,

Just following up to see if the Response provided by community members were helpful in addressing the issue.  If the issue still persists, we kindly request you to share the sample data in a workable format such as text, an Excel file, or a PBIX file with sample data instead of screenshots. Additionally, please include the expected output. This will enable us to assist you more effectively.

Thanks & Regards,

Prasanna Kumar

[Anonymous]

Hi @DineshArivu,

Just following up to see if the Response provided by community members were helpful in addressing the issue.  If the issue still persists, we kindly request you to share the sample data in a workable format such as text, an Excel file, or a PBIX file with sample data instead of screenshots. Additionally, please include the expected output. This will enable us to assist you more effectively.

Thanks & Regards,

Prasanna Kumar

[DineshArivu]

Hi Experts,

As per my requirement, I have to showcase rolling 4 hours average MIPS values - MIPS = Processing power of your mainframe (higher = more work being done)
I have already created a measure and applied in a new sample page with sample columns and it looks OK when I used to add with few columns with external filters as below :

1st 3 rows blank as we don't have strictly 4 rows previously (12am,1am,2am only there) 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 :

it should work with or without any external filters applied .

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

[jgeddes]

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]

@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