Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nhol
Advocate II
Advocate II

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

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

7 REPLIES 7
v-jiascu-msft
Employee
Employee

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.

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

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.

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.

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.

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.

@v-jiascu-msft Hi Dale,

I have a similar question with Average.

My data table is as follows :

qwerty12345_0-1638346091050.png

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.