cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nhol Member
Member

DAX - Dynamic Average of the last 10 Days

Hi,

 

Below is my data set and I'm trying to accomplish a dynamic average calculation. I need to come up with a DAX formula that will calculate dynamically every day the average [Rate] of the last 10 days:

 

How this can be done?

 

Thanks!

Nir H

 

Dynamic Average.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
v-jiascu-msft Super Contributor
Super Contributor

Re: DAX - Dynamic Average of the last 10 Days

Hi @nhol,

 

Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

 

Best Regards!
Dale

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

View solution in original post

6 REPLIES 6
v-jiascu-msft Super Contributor
Super Contributor

Re: DAX - Dynamic Average of the last 10 Days

Hi Nir,

 

You can try it out in this file.

1. As a measure.

Measure =
VAR currentDate =
    MIN ( 'Table1'[Date] )
VAR startDate = currentDate - 9
RETURN
    IF (
        CALCULATE ( MIN ( 'Table1'[Date] ), ALL ( 'Table1' ) ) > startDate,
        BLANK (),
        AVERAGEX (
            FILTER (
                ALL ( 'Table1' ),
                'Table1'[Date] <= currentDate
                    && 'Table1'[Date] >= startDate
            ),
            'Table1'[Rate]
        )
    )

2. As a calculated column.

1)

AverageRate =
VAR currentDate = 'Table1'[Date]
VAR startDate = currentDate - 9
RETURN
    IF (
        MIN ( 'Table1'[Date] ) > startDate,
        BLANK (),
        CALCULATE (
            AVERAGE ( Table1[Rate] ),
            FILTER (
                ALL ( 'Table1' ),
                'Table1'[Date] <= currentDate
                    && 'Table1'[Date] >= startDate
            )
        )
    )

Or 2)

AverageRate2 =
VAR currentDate = 'Table1'[Date]
VAR startDate = currentDate - 9
RETURN
    IF (
        MIN ( 'Table1'[Date] ) > startDate,
        BLANK (),
        AVERAGEX (
            FILTER (
                'Table1',
                'Table1'[Date] <= currentDate
                    && 'Table1'[Date] >= startDate
            ),
            'Table1'[Rate]
        )
    )

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
nhol Member
Member

Re: DAX - Dynamic Average of the last 10 Days

Thank you so much Dale!

Actually this was perfect until I realized that my dataset of [Dates] skips weekends and only capture buisness days. So I don'y really have a full continuityit by dates and basically every 5 days it skips to the next week starting date. This also apears in holidays where I have no date because it is not a business day.

I like the way you approached it but the (-9) thing probably won't work in this case.

Any other option?

 

By the way, I used your first option as a column (2.1)

 

(The [Average Last 10 Business Days] is a calculation I did manually in my Excel sheet)

Capture.PNG

 

 

Thanks again!

Nir H

Highlighted
v-jiascu-msft Super Contributor
Super Contributor

Re: DAX - Dynamic Average of the last 10 Days

Hi @nhol,

 

Two questions.

1. Are there records for non-business day? For example, 5th November 2017 is Sunday, is there a record for this day?

2. 1 Nov - 12 Nov are only 8 workdays. 

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
nhol Member
Member

Re: DAX - Dynamic Average of the last 10 Days

1. Nope. there are no records for non-business days so basically the data is sequential (just skipping days where data=[blank]).

2. you are right my mockup is bad but you got the idea...

 

One other thing that I realized just recently is the fact that when I'm using DirectQuery (because I need real-time data) PowerBI limits me in implementing any data query and I have to do it in the database. Not sure why PowerBI has this limitation.

 

Thanks!

Nir H.

v-jiascu-msft Super Contributor
Super Contributor

Re: DAX - Dynamic Average of the last 10 Days

Hi Nir,

 

You can try a measure like this. 

Measure =
VAR currentDate =
    MIN ( 'Table1'[Date] )
VAR startDate = currentDate - 13
RETURN
    IF (
        CALCULATE ( MIN ( 'Table1'[Date] ), ALL ( 'Table1' ) ) + 10
            < currentDate,
        AVERAGEX (
            FILTER (
                ALL ( 'Table1' ),
                'Table1'[Date] <= currentDate
                    && 'Table1'[Date] >= startDate
            ),
            'Table1'[value]
        ),
        BLANK ()
    )

DAX_Dynamic_Average_of_the_last_10_Days

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-jiascu-msft Super Contributor
Super Contributor

Re: DAX - Dynamic Average of the last 10 Days

Hi @nhol,

 

Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

 

Best Regards!
Dale

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

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 322 members 3,345 guests
Please welcome our newest community members: