Skip to main content
cancel
Showing results for 
Search instead 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

Reply
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:

Cumulative and 7 day keep getting.PNG

 

Where I was hoping to have something like this?

 

Cumulative and 7 day.PNG

 

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?
 
Thanks in advance
 
Please find file attached
4 REPLIES 4
daxer-almighty
Solution Sage
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,
            [Your Measure]
        )
    )
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,
            [Your Measure]
        )
    )
return
    __average
daxer-almighty
Solution Sage
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.

mahoneypat
Microsoft Employee
Microsoft Employee

Please try this measure expression

 

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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors