Forum Discussion

likensj's avatar
likensj
Frequent Visitor
7 years ago

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 typ...
Need Help
likensj's avatar
likensj
Frequent Visitor
7 years ago

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/2019100 - MEDICARE  $                                                                        -  No Payment
4/2/2019101 - MEDICARE HMO  $                                                                        -  No Payment
4/2/2019700 - CONTRACTED INS & HMO132 $                                                               202.73Payment
4/2/2019101 - MEDICARE HMO  $                                                                        -  No Payment
4/2/2019101 - MEDICARE HMO90 $                                                               271.71Payment
4/2/2019300 - NON-CONT INS & HMO  $                                                                        -  No Payment
4/2/2019200 - MEDICAID91 $                                                                        -  No Payment
4/2/2019101 - MEDICARE HMO129 $                                                               322.68Payment
4/2/2019100 - MEDICARE63 $                                                               322.67Payment
4/2/2019900 - VETERANS ADMINISTRATION  $                                                                        -  No Payment
4/2/2019400 - PRIVATE PAY  $                                                                        -  No Payment
4/2/2019101 - MEDICARE HMO129 $                                                               378.96Payment
4/2/2019101 - MEDICARE HMO90 $                                                                        -  No Payment
4/2/2019100 - MEDICARE63 $                                                               322.67Payment
4/3/2019100 - MEDICARE62 $                                                               322.67Payment
4/3/2019400 - PRIVATE PAY  $                                                                        -  No Payment
4/3/2019700 - CONTRACTED INS & HMO  $                                                                        -  No Payment
4/4/2019700 - CONTRACTED INS & HMO  $                                                                        -  No Payment
4/4/2019100 - MEDICARE68 $                                                               322.67Payment
4/4/2019400 - PRIVATE PAY  $                                                                        -  No Payment
4/4/2019100 - MEDICARE67 $                                                               322.67Payment
4/4/2019100 - MEDICARE68 $                                                               322.67Payment
4/4/2019100 - MEDICARE67 $                                                               322.67Payment
4/4/2019101 - MEDICARE HMO62 $                                                                 83.89Payment
4/4/2019200 - MEDICAID89 $                                                                        -  No Payment
4/4/2019100 - MEDICARE119 $                                                               322.67Payment
4/4/2019100 - MEDICARE61 $                                                               322.67Payment
4/4/2019101 - MEDICARE HMO133 $                                                                        -  No Payment
4/4/2019200 - MEDICAID118 $                                                                        -  No Payment
4/4/2019101 - MEDICARE HMO127 $                                                               143.58Payment
4/4/2019100 - MEDICARE67 $                                                               322.67Payment
4/4/2019200 - MEDICAID  $                                                                        -  No Payment
4/4/2019101 - MEDICARE HMO67 $                                                                 83.89Payment
4/4/2019200 - MEDICAID83 $                                                               450.79Payment
4/4/2019101 - MEDICARE HMO104 $                                                           1,300.00Payment
4/4/2019700 - CONTRACTED INS & HMO39 $                                                               367.50Payment
4/4/2019200 - MEDICAID118 $                                                                        -  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's avatar
dax
Community Support
7 years ago

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.