Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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/
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
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/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
54 | |
27 | |
23 | |
14 | |
11 |
User | Count |
---|---|
77 | |
63 | |
44 | |
17 | |
12 |