Forum Discussion

powerbiexpert22's avatar
powerbiexpert22
Icon for Impactful Individual rankImpactful Individual
2 years ago
Solved

Rolling 5 Days Average

Hi, I want to show the rolling 5 days Average like below, it should reset after every 5 days

 

  • tamerj1's avatar
    tamerj1
    2 years ago

    powerbiexpert22

     Rolling 5 Days Average =
    IF (
    HASONEVALUE ( 'Date'[Date] ),
    VAR FirstSelectedDate =
    CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED () )
    VAR CurrentDate =
    VALUES ( 'Date'[Date] )
    VAR DaysDifference =
    INT ( CurrentDate - FirstSelectedDate )
    VAR DayIndex =
    MOD ( DaysDifference, 5 )
    VAR Dates =
    DATESINPERIOD ( 'Date'[Date], CurrentDate, - DayIndex, DAY )
    RETURN
    COALESCE ( AVERAGEX ( Dates, [Sales Amont] ), [Sales Amont] )
    )

3 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi powerbiexpert22 
    Please try

    Rolling 5 Days Average =
IF (
    HASONEVALUE ( 'Date'[Date] ),
    VAR FirstSelectedDate =
        CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED () )
    VAR CurrentDate =
        VALUES ( 'Date'[Date] )
    VAR DaysDifference =
        INT ( CurrentDate - FirstSelectedDate )
    VAR DayIndex =
        MOD ( DaysDifference, 5 )
    VAR Dates =
        DATESINPERIOD ( 'Date'[Date], CurrentDate, - DayIndex, DAY )
    RETURN
        AVERAGEX ( Dates, [Sales Amont] )
)
    • powerbiexpert22's avatar
      powerbiexpert22
      Icon for Impactful Individual rankImpactful Individual
      2 years ago

      Hi tamerj1 , I am getting correct results except for first rows which are showing blank like below

       

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        2 years ago

        powerbiexpert22

         Rolling 5 Days Average =
        IF (
        HASONEVALUE ( 'Date'[Date] ),
        VAR FirstSelectedDate =
        CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED () )
        VAR CurrentDate =
        VALUES ( 'Date'[Date] )
        VAR DaysDifference =
        INT ( CurrentDate - FirstSelectedDate )
        VAR DayIndex =
        MOD ( DaysDifference, 5 )
        VAR Dates =
        DATESINPERIOD ( 'Date'[Date], CurrentDate, - DayIndex, DAY )
        RETURN
        COALESCE ( AVERAGEX ( Dates, [Sales Amont] ), [Sales Amont] )
        )