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
dax's avatar
dax
Community Support
7 years ago

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'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.