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
Highlighted
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.
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

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.
Highlighted
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.

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

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

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 349 members 3,537 guests
Please welcome our newest community members: