Forum Discussion
Aggregate Patient Claims when Exceeding Threshold
Hi novotnajk
Here is my .pbix https://1drv.ms/u/s!AgCd7AyfqZtE3zw8aPY2ndPJl7a4?e=mmShevI believe that this solves both questions,
Thank you,
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.
NathanielHi Nathaniel!
We fixed all but one of the problems! 🙂 I'm going to repost the outstanding issue. Thanks for your help!
Try this:
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
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
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.
NathanielHi Nathaniel, see the screenshot. For some reason all of the spend starts in January, but that isn't the case in the raw data.