cancel
Showing results for
Did you mean:
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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
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
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.
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)

Thanks again!

Nir H

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

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 ()
)```

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

Announcements

#### Win Power BI Swag with Community Kudopalooza!

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

#### Power Platform Summit North America

Register by September 5 to save \$200

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

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 349 members 3,537 guests
Recent signins: