Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to 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.
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
Proud to be a Super User!
@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.
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
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
@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 )
)
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
@Anonymous It should work. Is it possible for you to share PBIX after removing sensitive data?
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
@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.
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.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |