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] )
    )

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] )
      )