Unique Average and Calculated Field Scenario

[likensj]

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?

[dax]

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 Zhi

[likensj]

Below 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 Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.