30 day rolling average

[Anonymous]

Hi

Would anyone be able to help me convert this formula into a "last 30 day rolling average"? I seem to be going in circles.

Thank you in advance!

Turnover Rolling Avg. Calc. (€) =

AVERAGEX (

FILTER (

ALLSELECTED ( 'Calendar' ),

'Calendar'[Date] <= MAX ( 'Calendar'[Date] )

),

CALCULATE ( onetouch_daily_account_summary_last_2_months[Turnover (€)] )

)

[ALLUREAN]

Hi, @Anonymous 

You can try this one:

Turnover Rolling Avg. Calc. (€) =

VAR NumOfDays = 30
VAR LastCurrentDate = MAX ( 'Calendar'[Date] )
VAR Period = DATESINPERIOD ( 'Calendar'[Date], LastCurrentDate, - NumOfDays, DAY )
VAR Result =
CALCULATE (
AVERAGEX (
VALUES ( 'Calendar'[Date] ),
onetouch_daily_account_summary_last_2_months[bet_eur]), Period)

RETURN
Result

[Anonymous]

@Samarth_18  So now there is no error message, but the result is blank. hmm.

i did have to change the reference for this part, to the column, and not the measure, for it to accept it.  

AVERAGE ( onetouch_daily_account_summary_last_2_months[Turnover (€)] )

changed to 

AVERAGE ( onetouch_daily_account_summary_last_2_months[bet_eur] )

[Samarth_18]

Hi @Anonymous ,

You can try below code:-

Turnover Rolling Avg. Calc. (€) =
AVERAGEX (
    FILTER (
        ALLSELECTED ( 'Calendar' ),
        DATESINPERIOD ( 'Calendar'[Date], MAX ( 'Calendar'[Date] ), -30, DAY )
    ),
    CALCULATE ( onetouch_daily_account_summary_last_2_months[Turnover (€)] )
)

Thanks,

Samarth

[Anonymous]

Thank you for your reply @Samarth_18 . 

Unfortunately, i get an error message with your formula. 

[Samarth_18]

@Anonymous Try this:-

Turnover Rolling Avg. Calc. (€) =
CALCULATE (
    AVERAGE ( onetouch_daily_account_summary_last_2_months[Turnover (€)] ),
    ALL ( 'Calendar' ),
    DATESINPERIOD ( 'Calendar'[Date], MAX ( 'Calendar'[Date] ), -30, DAY )
)

[Anonymous]

@Samarth_18 Now i dont have an error message but i get a blank answer.

I changed this "AVERAGE ( onetouch_daily_account_summary_last_2_months[Turnover (€)] )" from a measure to reference the column (or else it wouldnt accept the formula)

I dont know if that makes a difference or not. 

Turnover Rolling Avg. Calc. (€) 2 =

CALCULATE (

AVERAGE ( onetouch_daily_account_summary_last_2_months[bet_eur] ),

ALL ( 'Calendar' ),

DATESINPERIOD ( 'Calendar'[Date], MAX ( 'Calendar'[Date] ), -30, DAY )

)

 

[Samarth_18]

@Anonymous It should work. Is it possible for you to share PBIX after removing sensitive data?

[Anonymous]

@Samarth_18 It will take me ages to clean this file to get it to a place can send 😞

Is this my only formula option or perhaps is there an alternate way using a different function perhaps? Maybe thats asking too much, but i wont be able to send the file just yet. hmm.

[Anonymous · Accepted solution]

Hi @Anonymous ,

I think you can try this code.

Turnover Rolling Avg. Calc. (€) 2 =
CALCULATE (
    AVERAGE ( onetouch_daily_account_summary_last_2_months[bet_eur] ),
    FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
            && 'Calendar'[Date]
                > MAX ( 'Calendar'[Date] ) - 30
    )
)

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.