How to create 7 day Rolling Average for created measure

[jovendeluna21]

Hello,

Anyone can help me to create a 7 Day Rolling Average measure for a specific field "daily distribution" which is a created measure. 7 Day Rolling Average of Daily Distribution. Thank you!

Herewith the pbi file

https://drive.google.com/file/d/1lEnkJ6I3O67zWyUhgf2cYjrbDXg8AXPP/view?usp=sharing

Thank you very much!

[v-lionel-msft · Accepted solution]

Hi @jovendeluna21 ,

Is this what you want?

Measure 2 = 
VAR x = 
SUMX(
    FILTER(
        ALLSELECTED(us_state_vaccinations),
        us_state_vaccinations[date] <= MAX(us_state_vaccinations[date]) && us_state_vaccinations[date] >= MAX(us_state_vaccinations[date]) - 6
    ),
    [Daily Distribution]
)
RETURN
x/7

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[edhans]

@jovendeluna21 try this measure, but your model needs a lot of work.

7 Day Average = 
VAR varCurrentDay = MAX('Calendar Table'[Date])
RETURN
CALCULATE(
    AVERAGE(us_state_vaccinations[total_distributed]),
    DATESBETWEEN('Calendar Table'[Date], varCurrentDay-7, varCurrentDay)
)

It returns this:

This is date time intelligence, and it requires a date table, however, neither your State Data nor us_state_vaccination tables were connected to the date table, so I altered your model by doing so. this is a layout of just that view. You'll notice I then hid the dates in both of the FACT tables (the state tables) - don't use those dates in your visuals. Use the dates in the date table - always use fields from the DIM table except the analysis values are you looking at in the fact table.

Then I removed the date from your table which was from one of the state tables (you cannot use time intelligence with date fields in non-date tables) and used the date from the Calendar/date table.

Now the math just works.

You will notice there is now a blank row. This means you have values in one or both of your state tables that is outside of the range of the dates in the calendar table. Expand the dates in the calendar table. You can do this dynamically using Power Query - Creating a Dynamic Date Table in Power Query if you want.

Lastly, you have 2 bidirectional filters in your table. Get rid of those. Use 1 to many. Microsoft recommends minimizing use of both Many-to-Many and Bi-Directional Relationships. In other words, unless you are a DAX expert, find another way to remodel your data to conform to a Star Schema and don't use these two features. I avoid them both at all costs.
Microsoft Guidance on Many-To-Many Relationships
Microsoft Guidance on Bi-Directional Relationships
Microsoft Guidance on Importance of Star Schema

I didn't investigate why you were using them, but you should use CROSSFILTER() in CALCULATE() to enable on a per measure basis, or use Filter measures if you are trying to sync slicers.

Here is your PBIX file back so you can see what I did. The crux of this is, if you are using a Star Schema, this is a very simple calculation. Read the above links for more info. It makes report building and DAX so much easier! 👍

EDIT: I realized your Total Distributions measure broke. I fixed it. It should also reference the DATE table, not a date field in another fact table.

Daily Distribution = 
VAR vThisDate =
    MAX ( 'Calendar Table'[Date] )
VAR vThisValue =
    SUM ( us_state_vaccinations[total_distributed] )
VAR vPrevValue =
    CALCULATE (
        LASTNONBLANKVALUE (
            'Calendar Table'[Date],
            CALCULATE (
                SUM ( us_state_vaccinations[total_distributed] )
            )
        ),
        ALL ( 'Calendar Table'[Date] ),
        'Calendar Table'[Date] < vThisDate
    )
VAR vResult = vThisValue - vPrevValue
RETURN
    vResult

That measure is more complicated than it needs to be. The ALL isn't truly necessary as a date table has an implicit ALL() around the date column. Part of Date table magic.

But as I said, a bit of work needs to be done on the model, so I've stopped here. It will rapidly turn into a project. 😉

[jovendeluna21]

what I need is the 7 day average of the daily distribution

[edhans]

Wouldn't you just modify the measure then to be this?

7 Day Average = 
VAR varCurrentDay = MAX('Calendar Table'[Date])
RETURN
DIVIDE(
    CALCULATE(
        [Daily Distribution],
        DATESBETWEEN('Calendar Table'[Date], varCurrentDay-6, varCurrentDay)
    ),
    7
)

My original measure subtracted 7 - that was giving an 8 day range. Needs to be -6. Now it adds the daily distributions up and divides by 7.

[amitchandak]

@jovendeluna21 , With help from a date table

Rolling 7 day = CALCULATE(sum(Table[daily distribution]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-7,Day))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[jovendeluna21]

Daily distribution doesn't work since it is a measure. Can't be sum.

Can you try with my pbi? Thanks!