Weighted Average

[czuniga]

I have a dataset similar to this: 

Claim #	DateSubmitted	Date Paid	# Of Days Between Date Submitted & DatePaid OR # Of Days Since Submitted
54321	3/1/2021	3/15/2021	14
54321	3/2/2021	3/16/2021	14
54321	3/3/2021	3/17/2021	14
54321	3/4/2021	3/18/2021	14
54321	3/5/2021	3/19/2021	14
54321	3/6/2021	3/20/2021	14
54321	3/7/2021	3/21/2021	14
54321	3/8/2021	3/22/2021	14
54321	3/9/2021	3/23/2021	14
54321	3/10/2021	3/24/2021	14
54321	3/11/2021	3/25/2021	14
54321	3/12/2021	3/26/2021	14
54321	3/13/2021	3/27/2021	14
54321	3/14/2021	3/28/2021	14
54321	3/15/2021	3/29/2021	14
54321	3/16/2021		13
54321	3/17/2021		12
54321	3/18/2021		11
54321	3/19/2021		10
54321	3/20/2021		9
54321	3/21/2021		8
54321	3/22/2021		7
54321	3/23/2021		6
54321	3/24/2021		5
54321	3/25/2021		4

I need to get the average of the "# Of Days Between Date Submitted & DatePaid OR # Of Days Since Submitted" that includes all of the rows with no pay date, but of the rows that do have a pay date, only those that have a pay date within the last 30 days. 

[selimovd · Accepted solution]

Hey @czuniga ,

the following measure should fulfil your criteria:

Average if not paid or 30 days =
CALCULATE(
    AVERAGE( myTable[# Of Days Between Date Submitted & DatePaid OR # Of Days Since Submitted] ),
    FILTER(
        myTable,
        myTable[Date Paid] = BLANK() || DATEDIFF( myTable[Date Paid], TODAY(), DAY ) <= 30
    )
)

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

[czuniga]

If I wanted to view this same information on a rolling basis, how could I do that?

I have this so far, but something is off: 

Average if not paid or 30 days = CALCULATE( AVERAGE( Claims[Avg Days Outstanding] ), FILTER(

ALLSELECTED(Claims ), Claims[DateService] <= max(Rolling_Calendar_Lookup[Date]) || DATEDIFF( Claims[PayDate], max(Rolling_Calendar_Lookup[Date]), DAY ) <= 30 ) )

[czuniga]

Worked perfectly. Thank you!