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