Creating a table with average rolling 7-day daily summed revenue

[riegersn]

I'm trying to calculate revenue forecast, this is just the first part of it. I'm trying to calculate the daily average revenue over the past 7 days. Multiple revenue entries per asset a day so I need to sum revenue by asset per day first. This works if I create a calculated table that just returns the first summarize (for daily sum of last 7 days), then create another calculated table to average those days by asset. However, I'd like to get this into a single table. I've tried a few things I'm finding on the forums but not having much luck.
The last line breaks on the AVERAGE() function, I can't get it to reference the [Revenue] column from the LAST7TAB SUMMARIZE. Any suggestions?

Average Daily Rev = 
VAR Last = CALCULATE(LASTDATE('Date'[Date])-1,'Date'[IsFuture] = FALSE())
VAR LAST7TAB = SUMMARIZE(
        FILTER('Daily Revenue', 'Daily Revenue'[Date] > Last - 7 && 'Daily Revenue'[Date] <= Last),
        'Daily Revenue'[Asset], 'Daily Revenue'[Date],
        "Revenue",
        SUM('Daily Revenue'[Revenue])
    )
RETURN
SUMMARIZE(LAST7TAB, [Asset], "7Day", AVERAGE([Revenue])) // <-- This isn't working

[VahidDM · Accepted solution]

Hi @riegersn 

Try this:

Average Daily Rev =
VAR Last =
    CALCULATE ( LASTDATE ( 'Date'[Date] ) - 1, 'Date'[IsFuture] = FALSE () )
VAR LAST7TAB =
    SUMMARIZE (
        FILTER (
            'Daily Revenue',
            'Daily Revenue'[Date] > Last - 7
                && 'Daily Revenue'[Date] <= Last
        ),
        'Daily Revenue'[Asset],
        'Daily Revenue'[Date],
        "Revenue", SUM ( 'Daily Revenue'[Revenue] )
    )
RETURN
    SUMMARIZE ( LAST7TAB, [Asset], "7Day", AVERAGEX ( LAST7TAB, [Revenue] ) )

if it does not work, try to use GROUPBY.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[VahidDM · Accepted solution]

Hi @riegersn 

Try this:

Average Daily Rev =
VAR Last =
    CALCULATE ( LASTDATE ( 'Date'[Date] ) - 1, 'Date'[IsFuture] = FALSE () )
VAR LAST7TAB =
    SUMMARIZE (
        FILTER (
            'Daily Revenue',
            'Daily Revenue'[Date] > Last - 7
                && 'Daily Revenue'[Date] <= Last
        ),
        'Daily Revenue'[Asset],
        'Daily Revenue'[Date],
        "Revenue", SUM ( 'Daily Revenue'[Revenue] )
    )
RETURN
    SUMMARIZE ( LAST7TAB, [Asset], "7Day", AVERAGEX ( LAST7TAB, [Revenue] ) )

if it does not work, try to use GROUPBY.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[riegersn · Accepted solution]

Thank you! Your example did not work however using groupby instead of summarize for the return worked perfectly!

Average Daily Rev = 
VAR Last =
    CALCULATE ( TODAY() - 1, 'Date'[IsFuture] = FALSE() )

VAR ROLLING7DAY =
    SUMMARIZECOLUMNS (
        'Daily Revenue'[Asset],
        'Daily Revenue'[Date],
        "Revenue",
        SUMX (
            FILTER (
                'Daily Revenue',
                'Daily Revenue'[Date] > Last - 7
                    && 'Daily Revenue'[Date] <= Last ),
            'Daily Revenue'[Revenue]
        )
    )

RETURN

GROUPBY (
    ROLLING7DAY,
    'Daily Revenue'[Asset],
    "Average Revenue",
    AVERAGEX ( CURRENTGROUP(), [Revenue] )
)

[VahidDM]

Hi @riegersn 

Happy to see you find the solution.

If my post helps you to find a solution, please consider accepting it and your post as the solutions to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/