Forum Discussion

novotnajk's avatar
novotnajk
Resolver 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
Community 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

novotnajk's avatar
novotnajk
Resolver 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.