Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I'm looking for some assistance in creating a "4-week" rolling average. It's actually going to be a 4-data point rolling average as my snapshots are not always exactly 4-weeks apart. My data has goals going out to the end of the year which seems to drag my rolling average out with it. Below is the data of what I have which creates a plot with a dropping average into the future. The averages seem to be calculating correctly, but I'd like to have them not project out past dates for which I have an amount for. Is there a way to remove the future datapoints from my measure?
Solved! Go to Solution.
Sorry. I thought [Amount] was a measure in your original shown table. You can try to replace that with ISBLANK(SUM('CW Dataset'[Amount])).
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@rnoble
Try your measure with this modification":
4-week average =
VAR last4weeksofsales =
CALCULATETABLE (
TOPN (
4,
SUMMARIZE (
'CW Dataset',
'CW Dataset'[As of date],
"Amount", SUM ( 'CW Dataset'[Amount] )
),
'CW Dataset'[As of date], DESC
),
FILTER (
ALL ( 'Date'[As of date] ),
'Date'[As of date] <= SELECTEDVALUE ( 'Date'[As of date] )
)
)
var __Amount = SUMX ( last4weeksofsales, [Amount] )
RETURN
IF(
ISBLANK(__Amount),
BLANK(),
DIVIDE ( __Amount, COUNTROWS ( last4weeksofsales ) )
)
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Have you tried just changing the ISBLANK in your return to ISBLANK([Amount])? That way, any date that does not have an Amount value will not show the 4-day average.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thanks for the reply @mahoneypat! I get an error when I try that: The value for 'Amount' cannot be determined. Either the column doesn't exist, or there is no current row for this column.
Sorry. I thought [Amount] was a measure in your original shown table. You can try to replace that with ISBLANK(SUM('CW Dataset'[Amount])).
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@Fowmy Thanks for taking a look. I tried what you suggested and nothing changed. Other thoughts? Am I asking the wrong question?
User | Count |
---|---|
57 | |
21 | |
19 | |
17 | |
16 |
User | Count |
---|---|
85 | |
80 | |
52 | |
37 | |
22 |