cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Adding 2 custom lines to line and stacked column chart

I currently have a pareto chart by number of cases per event date.

I'm trying to add a rolling 7 day average calculation to this but I keep getting:

Where I was hoping to have something like this?

I'm currently running my 7 day rolling average by [Event Date]:

7 Day Rolling Average - Notification Date =
CALCULATE(SUM('Sheet1'[Epi Date Count]),DATESINPERIOD('Sheet1'[Event Date],LASTDATE('Sheet1'[Event Date]),7,DAY))
/
CALCULATE(DISTINCTCOUNT('Sheet1'[Event Date]),DATESINPERIOD('Sheet1'[Event Date],LASTDATE('Sheet1'[Event Date]),7,DAY))

Is there any way this can be done?

4 REPLIES 4
Solution Sage

I'll show you how to do a 7-day rolling average of any measure but this will be on the assumption that your model is correct (currently it is not, please refer to the link about star-schema I talked about before). For this to work, you have to have a Date table (call it 'Event Date') that'll be connected to your fact table on the 'Event Date'[Date] field (joining to Fact[EventDate]).

``````// Please remember that in a correct model
// slicing and dicing is only ever performed
// by selections from dimensions, never
// fact tables. The latter must always be
// hidden (at least all their columns must be,
// measures are OK to remain exposed as long
// as they are not helper measures that should
// indeed be hidden as well).

[7D Rolling Avg] =
var __daysCountRequired = 7
// First, we have to check
// if there are at least 7
// days available to calculate
// the average.
var __lastVisibleDay = max( 'Event Dates'[Date] )
// Please make sure that 'Event Dates' is
// marked as a Date table in the model.
var __periodToAverageOver =
DATESINPERIOD(
'Event Dates'[Date],
__lastVisibleDay,
-(__daysCountRequired - 1),
DAY
)
// You have to check this condition
// because the date table starts
// on some day and if you are too close
// to it, there will not be 7 full days
// available to calculate the average.
var __shouldCalculate =
COUNTROWS( __periodToAverageOver ) = __daysCountRequired
// Please bear in mind that if for a
// particular day the value of the measure
// [Your Measure] is BLANK, this value
// is not taken into account. In other words,
// the average of 1, blank, 2 is (1 + 2)/2.
// If you want to treat blank as 0 so that
// the average of 1, blank, 2 is (1 + 0 + 2) / 3
// then add 0 to the measure under AVERAGEX.
var __average =
if( __shouldCalculate,
AVERAGEX(
__periodToAverageOver,
)
)
return
__average``````

Of course, you could also want the 7D average but only for those days that do have a non-blank measure. If this is the case, you could use this code:

``````[7D Rolling Avg] =
var __daysCountRequired = 7
var __lastVisibleDay = max( 'Event Dates'[Date] )
// The assumption here is that if there is
// an entry in the FactTable for a particular
// day, it means the measure you're trying to
// average is not blank for this day.
var __periodToAverageOver =
TOPN(__daysCountRequired,
SUMMARIZE(
CALCULATETABLE(
FactTable,
'Event Dates'[Date] <= __lastVisibleDay
),
'Event Dates'[Date]
),
'Event Dates'[Date],
DESC
)
var __shouldCalculate =
COUNTROWS( __periodToAverageOver ) = __daysCountRequired
var __average =
if( __shouldCalculate,
AVERAGEX(
__periodToAverageOver,
)
)
return
__average``````
Solution Sage

Hi there.

I wouldn't like to bother you, @Anonymous... but you are most likely (as much as I can judge by your posts) making a big mistake that is so common to inexperienced practitioners of Power BI: You are keeping all the data in one big table. This is a no-no in this trade. Please read this to find out first how to properly structure your data for analysis.

Then you'll probably know better what to do to get what you want. Please always keep in mind that each decent and professional model needs at least one Date table. Do not ever use a column from a fact table in your reports. Fact tables should always be hidden. ALWAYS.

Microsoft Employee

Avg 7 Day =
VAR thisdate =
MAX(Sheet1[Event Date])
RETURN
CALCULATE (
AVERAGEX(VALUES(Sheet1[Event Date]), CALCULATE(COUNT(Sheet1[Event ID]))),
FILTER(ALL ( 'Sheet1'[Event Date] ),
'Sheet1'[Event Date] <= thisdate && Sheet1[Event Date]>= thisdate-6))
Regards,
Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Anonymous
Not applicable

Still gives me the same result as the first picture unfortunately.