Weighted avreage with dynamic What if parameter

[TiemenHarms]

Hello, 

I want to calculate a weighted average with a dynamic What if parameter used in a measure. I have the following syntax

HT average dynamic period =
AVERAGEX (
    DATESBETWEEN (
        'Date'[Date],
        MAX ( 'Date'[Date] ) - [Waarde van Parameter],
        MAX ( 'Date'[Date] )
    ),
    'Fact table'[Average handling time]
)

I have attached a simple pbix file with a dummy data of the model. I want to show the HT dynamic periode weighted bij the number of calls. 

https://1drv.ms/u/s!AouR1HCINaf0hKxpkzqauBaNEr9OZA

Does anybody know what Dax syntax i need to wrap around this to make it a weighted average?

Thank you. 

[AlexisOlson · Accepted solution]

I'm assuming you want to weight by the number of calls rather than the number of days.

HT average dynamic period =
VAR CurrDate = MAX ( 'Date'[Date] )
VAR StartDate = CurrDate - [Waarde van Parameter] + 1
RETURN
    CALCULATE (
        [Average handling time],
        DATESBETWEEN ( 'Date'[Date], StartDate, CurrDate )
    )

[TiemenHarms]

Hello @AlexisOlson 

Thank you for your reply. Yes I want to weight by the number of calls. The syntax you suggested shows the correct weighted average of Handling time.

Thank you!

[parry2k]

@TiemenHarms try this measure

HT average dynamic period v1 = 
VAR __endDate = MAX ( 'Date'[Date] ) 
VAR __startDate = __endDate - ( [Waarde van Parameter] - 1 )
RETURN
AVERAGEX (
    DATESBETWEEN (
        'Date'[Date],
        __startDate,
        __endDate
    ),
    'Fact table'[Average handling time]
)

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[TiemenHarms]

Hello @parry2k ,

Thank you for your reply. The syntax looks almost similar to the one Alexis posted but does not shows the correct weighted average bij the number of calls (i forgot to mention this in my original post). 

Thanks again for your time and help.