Forum Discussion
Solved
Weighted Average
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.
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 regardsDenisBlog: WhatTheFact.bi
3 Replies
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 regardsDenisBlog: WhatTheFact.biWorked perfectly. Thank you!
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 ) )
