Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Performance : calculating Moving average, percentile

Good morning, everyone, I would like to present to you a case where performance is poor. The context is this: a date dimension table (this one includes all dates with hh:mm:ss) + a fact table ("PDJ...
Need Help
  • AkhilAshok's avatar
    AkhilAshok
    7 years ago

    Can you try changing the last 2 bits:

     

    'Fact'[TimeID] >= MovingWindowStart
    && 'Fact'[TimeID] <= MovingWindowEnd

     

    to

     

    'DimTime'[TimeKey] >= MovingWindowStart
    && 'DimTime'[TimeKey] <= MovingWindowEnd

     

    In addition, make sure TimeKey should be an integer.

AkhilAshok's avatar
AkhilAshok
Icon for Solution Sage rankSolution Sage
7 years ago

You should have a Date dimension (365 rows for 1 year) & a time dimension (86400 rows for one day), and link it to your fact table.

 

Afterwards, you can derive your moving Average based on the Time dimension table which has all the time.

 

AverageMoving2s =
VAR MovingWindowEnd =
    MAX ( 'Time'[TimeID] )
VAR MovingWindowStart = MovingWindowEnd - 2
VAR MaxTimeinFact =
    CALCULATE ( MAX ( 'Fact'[TimeID] ), ALLSELECTED ( 'Time' ) )
VAR MinTimeinFact =
    CALCULATE ( MIN ( 'Fact'[TimeID] ), ALLSELECTED ( 'Time' ) )
RETURN
    IF (
        MovingWindowEnd <= MaxTimeinFact
            && MovingWindowEnd >= MinTimeinFact,
        CALCULATE (
            AVERAGEX (
                VALUES ( 'Time'[TimeID] ),
                VAR Total = [M1 Total] RETURN IF ( ISBLANK ( Total ), 0, Total )
            ),
            ALL ( 'Time' ),
            'Time'[TimeID] >= MovingWindowStart
                && 'Time'[TimeID] <= MovingWindowEnd
        )
    )
Anonymous's avatar
Anonymous
Not applicable
7 years ago

Thank you AkhilAshok !

 

It looked good! But the average doesn't work.

You can see below.

 

AverageMoving2sTest = 
VAR MovingWindowEnd =
    MAX ( 'DimTime'[TimeKey] )
VAR MovingWindowStart = MovingWindowEnd - 2
VAR MaxTimeinFact =
    CALCULATE ( MAX ( 'Fact'[TimeID] ); ALLSELECTED ( 'DimTime' ) )
VAR MinTimeinFact =
    CALCULATE ( MIN ( 'Fact'[TimeID] ); ALLSELECTED ( 'DimTime' ) )
RETURN
    IF (
        MovingWindowEnd <= MaxTimeinFact
            && MovingWindowEnd >= MinTimeinFact;
        CALCULATE (
            AVERAGEX (
                VALUES ( 'DimTime'[TimeKey] );
                VAR Total = [Total bets/s] RETURN IF ( ISBLANK ( Total ); 0; Total )
                     );
            ALL ('DimTime');
            'Fact'[TimeID] >= MovingWindowStart
                && 'Fact'[TimeID] <= MovingWindowEnd
                  )
    )

 

Regards,

  • AkhilAshok's avatar
    AkhilAshok
    Icon for Solution Sage rankSolution Sage
    7 years ago

    Can you try changing the last 2 bits:

     

    'Fact'[TimeID] >= MovingWindowStart
    && 'Fact'[TimeID] <= MovingWindowEnd

     

    to

     

    'DimTime'[TimeKey] >= MovingWindowStart
    && 'DimTime'[TimeKey] <= MovingWindowEnd

     

    In addition, make sure TimeKey should be an integer.

    • v-frfei-msft's avatar
      v-frfei-msft
      Icon for Community Support rankCommunity Support
      7 years ago

      Hi Anonymous,

       

      Does that make sense? Kindly mark the answer to close the case. Thanks in advance.

       

      Regards,

      Frank