Forum Discussion
Unique Average and Calculated Field Scenario
I have spent the better part of a day attempting to simulate many of the very good solutions found here. I am new to DAX and still trying to learn.
The scenario, I have a variety of different types of clients with different types of medical insurance, each insurance has its own average days to first payment, example below:
I have a another column that has the Date of Service for each claim, lastly I have a field that indicates whether a payment has or has not been made. What I am trying to do is for each claim that has no Payment, and based on the Payor type as noted above is project a payment date for the claim, So for Instance a Claim for Code 200-Medicaid, with an original date of service of 01/01/19 would have a projected payment date of 02/19/19, based on the current average days to pay for that Code.
While many of the posts discuss different averaging solutions on aggregates I am not able to find anything quite fitting this scenario. I did attempt to create a new table from the original but unfortunately all it will return is the average of the entire column of 49.04 days, and not the row level average as illustrated above.
Thoughts?
3 Replies
daxCommunity Support
Hi likensj,
I can't reproduce your design based on your description, so if possible, could you please inform me more detailed information(such as your data sample and your expecting output)? Then I will help you more correctly.
Please do mask sensitive data before uploading.
Thanks for your understanding and support.
Best Regards,
Zoe ZhiBelow is a sample of my data
[Date of Service] [Primary Bill-To Financial Class Combo] [cDays to First Payment from DOS] [cFirst Payment Amount] Payment 4/2/2019 100 - MEDICARE $ - No Payment 4/2/2019 101 - MEDICARE HMO $ - No Payment 4/2/2019 700 - CONTRACTED INS & HMO 132 $ 202.73 Payment 4/2/2019 101 - MEDICARE HMO $ - No Payment 4/2/2019 101 - MEDICARE HMO 90 $ 271.71 Payment 4/2/2019 300 - NON-CONT INS & HMO $ - No Payment 4/2/2019 200 - MEDICAID 91 $ - No Payment 4/2/2019 101 - MEDICARE HMO 129 $ 322.68 Payment 4/2/2019 100 - MEDICARE 63 $ 322.67 Payment 4/2/2019 900 - VETERANS ADMINISTRATION $ - No Payment 4/2/2019 400 - PRIVATE PAY $ - No Payment 4/2/2019 101 - MEDICARE HMO 129 $ 378.96 Payment 4/2/2019 101 - MEDICARE HMO 90 $ - No Payment 4/2/2019 100 - MEDICARE 63 $ 322.67 Payment 4/3/2019 100 - MEDICARE 62 $ 322.67 Payment 4/3/2019 400 - PRIVATE PAY $ - No Payment 4/3/2019 700 - CONTRACTED INS & HMO $ - No Payment 4/4/2019 700 - CONTRACTED INS & HMO $ - No Payment 4/4/2019 100 - MEDICARE 68 $ 322.67 Payment 4/4/2019 400 - PRIVATE PAY $ - No Payment 4/4/2019 100 - MEDICARE 67 $ 322.67 Payment 4/4/2019 100 - MEDICARE 68 $ 322.67 Payment 4/4/2019 100 - MEDICARE 67 $ 322.67 Payment 4/4/2019 101 - MEDICARE HMO 62 $ 83.89 Payment 4/4/2019 200 - MEDICAID 89 $ - No Payment 4/4/2019 100 - MEDICARE 119 $ 322.67 Payment 4/4/2019 100 - MEDICARE 61 $ 322.67 Payment 4/4/2019 101 - MEDICARE HMO 133 $ - No Payment 4/4/2019 200 - MEDICAID 118 $ - No Payment 4/4/2019 101 - MEDICARE HMO 127 $ 143.58 Payment 4/4/2019 100 - MEDICARE 67 $ 322.67 Payment 4/4/2019 200 - MEDICAID $ - No Payment 4/4/2019 101 - MEDICARE HMO 67 $ 83.89 Payment 4/4/2019 200 - MEDICAID 83 $ 450.79 Payment 4/4/2019 101 - MEDICARE HMO 104 $ 1,300.00 Payment 4/4/2019 700 - CONTRACTED INS & HMO 39 $ 367.50 Payment 4/4/2019 200 - MEDICAID 118 $ - No Payment Essentially what I am attempt to do create a running average of Days to First Payment for each FInancial Combo class, this will obviously change as the payment pattern of each Financial Class Combo changes over time. Then for each date of service that has No Payment, I want to create a new column that projects a future payment date, i.e., Date of service+average Days to Pay = Projected Payment Date, our Team can then quicly identify those Dates of service that have a projected payment due but is not past the projected payment date. Does that help?
Thanks in advance,
Jason
- dax
Hi
You could try below measure to get rolling average
Measure 6 = CALCULATE ( SUM ( 'Table (3)'[cDays to First Payment from DOS] ), FILTER ( ALL ( 'Table (3)' ), 'Table (3)'[Primary Bill-To Financial Class Combo] = MIN ( 'Table (3)'[Primary Bill-To Financial Class Combo] ) && 'Table (3)'[Date of Service] <= MIN ( 'Table (3)'[[Date of Service] ) ) ) / CALCULATE ( DISTINCTCOUNT ( 'Table (3)'[Date of Service] ), FILTER ( ALL ( 'Table (3)' ), 'Table (3)'[Primary Bill-To Financial Class Combo] = MIN ( 'Table (3)'[Primary Bill-To Financial Class Combo] ) && 'Table (3)'[Date of Service] <= MIN ( 'Table (3)'[Date of Service] ) ) )In addition, I don't understand the Date of service+average Days to Pay = Projected Payment Date, so could you please inform me your expecting output? Then I will help you more correctly.
Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.