Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

30 day rolling average

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 (€)] )
)
1 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.

View solution in original post

9 REPLIES 9
ALLUREAN
Solution Sage
Solution Sage

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




Did I answer your question? Mark my post as a solution!


https://allure-analytics.com/
https://www.youtube.com/channel/UCndD_QZVNB_JWYLEmP6KrpA
https://www.linkedin.com/company/77757292/

Proud to be a Super User!




Anonymous
Not applicable

@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
Community Champion
Community Champion

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
Not applicable

Thank you for your reply @Samarth_18 . 

 

Unfortunately, i get an error message with your formula. Capture.PNG

@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
Not applicable

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

@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

Anonymous
Not applicable

@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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.