DAX - Dynamic Average of the last 10 Days

[nhol]

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

[v-jiascu-msft]

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

[nhol]

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

[v-jiascu-msft]

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

[nhol]

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]

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

[v-jiascu-msft · Accepted solution]

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

[qwerty12345]

@v-jiascu-msft Hi Dale,

I have a similar question with Average.

My data table is as follows :

I would like to calculate the average utilisation of the team for day shift and afternoon shift on a daily basis.

As you can see, the day shift team consists of 3 people : Alice, Sera and Peter

Afternoon shift consists of 2 people: Tina and Vivianne

What I ideally want on my chart: example : On 1 Dec 21, on day shift, Average team Utilisation =(6%+8%+9%+6%)/3

And similarly, lets say if one person is away, Average team utilisation on Day shift= (Sum of utilisation)/2.

Please provide me assistance on the formula for this. Thanks.