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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
riegersn
Helper I
Helper I

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

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

 

2 ACCEPTED SOLUTIONS
VahidDM
Super User
Super User

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/

 

 

View solution in original post

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

 

View solution in original post

3 REPLIES 3
VahidDM
Super User
Super User

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/

 

 

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

 

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/

 

 

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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