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
Nathaniel_C's avatar
Nathaniel_C
Icon for Community Champion rankCommunity Champion
4 years ago

Hi novotnajk ,

So taking this one step at a time, did we solve issue number 1?


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,

Step 1:  Removing CALCULATE from the $ Medical Spend was done.

It now reads:  

$ Medical Spend = (SUM(Medical[cost])
  • novotnajk's avatar
    novotnajk
    Icon for Resolver I rankResolver I
    4 years ago

    Step 2 for Cumulative Medical, the formula is this:

    $ Cumulative Medical Spend =
    var _currentDate = MAX('Reporting'[reporting_date])
    var _calc = CALCULATE([$ Medical Spend],FILTER(ALL('Reporting'[reporting_date]),'Reporting'[reporting_date] <=_currentDate))
    return _calc
     
    This did not work.  See screenshot.