Forum Discussion

novotnajk's avatar
novotnajk
Icon for Resolver I rankResolver I
4 years ago
Solved

Aggregate Patient Claims when Exceeding Threshold

Hi PBI Gurus!  Need some help.  Here's my dilemma:   Part 1 - Table Here is my data table example Special ID          reporting_date           $ Medical Spend ABC123              1/1/2021       ...
Need Help
Tips and Tricks
  • novotnajk's avatar
    novotnajk
    4 years ago

    Hi Nathaniel!

     

    We fixed all but one of the problems!  🙂  I'm going to repost the outstanding issue.  Thanks for your help!

Nathaniel_C's avatar
Nathaniel_C
Icon for Community Champion rankCommunity Champion
4 years ago

Try this:

novotnajk 

 

Running Total = 

var _currentDate = MAX('$ Medical Spend'[Reporting Date])

var _calc = CALCULATE([Medical Spend],FILTER(ALL('$ Medical Spend'[Reporting Date]),'$ Medical Spend'[Reporting Date]  <=_currentDate))



return _calc


Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

  • Nathaniel_C's avatar
    Nathaniel_C
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Here is a pic of the table.  And you don't need to use CALCULATE() in your first measure

    Medical Spend = SUM('$ Medical Spend'[ $ Medical Spend])


    Let me know if you have any questions.

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
    Nathaniel

    • Nathaniel_C's avatar
      Nathaniel_C
      Icon for Community Champion rankCommunity Champion
      4 years ago

      Hi novotnajk , 
      The issue is that in your query, you use the MAX() date.  You need to capture the date on the row that is currently being evaluated before you use the CALCULATE() and refer to it in your FILTER.

      Hopes this helps,


      Let me know if you have any questions.

      If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
      Nathaniel

      • novotnajk's avatar
        novotnajk
        Icon for Resolver I rankResolver I
        4 years ago

         

        Hi Nathaniel, see the screenshot.  For some reason all of the spend starts in January, but that isn't the case in the raw data.

  • novotnajk's avatar
    novotnajk
    Icon for Resolver I rankResolver I
    4 years ago

    Hi Nathaniel,

     

    Thanks for the advice.  I created the measure below:

    $ Cumulative Medical Spend =
    var _currentDate = MAX('Medical'[reporting_date])
    var _calc = CALCULATE([$ Medical Spend],FILTER(ALL('Medical'[reporting_date]),'Medical'[reporting_date] <=_currentDate))
    return _calc
     
    But it didn't show all of the claims based on the ISL slicer.  Also, the Medical Spend measure has the calculate in front because the data change each month.  Does that matter?
     
    I also can't get the $ SL Medical Spend formula do cumulate based on the filtered value either.  I created a sample PBIX, but not sure how to attach it here.