Aggregate Patient Claims when Exceeding Threshold

[novotnajk]

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                    $25,000 

CDF123              2/1/2021                    $15,000

ABC123              2/1/2021                    $15,000   

XYZ123              2/1/2021                    $10,000         

ABC123             3/1/2021                     $5,000

The Reporting Dates are an append from multiple tables and in a separate table called 'Reporting'[reporting_date])

I created a measure called Medical Spend from the medical table $ Medical Spend = CALCULATE(SUM('Medical'[medical_cost])

Part 2 - Table with Limits

I have a table with values that are in $5K increments call 'ISL' [isl_limit].  I created a slicer visual that allows me to select a value (i.e., $5,000, $25,000, etc.).

Part 3 - Total Medical Spend

I created a table with the Reporting Date and the medical spend by month:

 Quarter     Month      $ Medical Spend

  Q1            January     $25,000

  Q1            February   $40,000

  Q1            March       $5,000

Help #1 - Cumulative Total Medical Spend

I created a formula that I would hope create the following results, but it does not work:

 Quarter     Month      $ Cumulative Medical Spend

  Q1            January     $25,000

  Q1            February   $65,000

  Q1            March       $70,000

$ Cumulative Medical Spend =

CALCULATE([$ Medical Spend],

FILTER (

ALL ('Reporting'[reporting_date]),

'Reporting'[reporting_date] <= MAX('Reporting'[reporting_date] )

))

This formula doesn't work.  HELP #1 Please! 🙂

Help #2 - Cumulative Total Medical Spend that Exceeds the ISL

First I created a measure called $ SL = CALCULATE(SUM('ISL' [isl_limit]) and created a drop down filter

Second, I created a second measure that I would hope show the cumulative amounts of members who exceed the $SL value, thus producing a table like this (where the SL value is for total medical costs for all members > $25K in spend):

 Quarter     Month      $ Cumulative Medical Spend     $ SL Medical Spend

  Q1            January     $25,000                                      $25,000

  Q1            February   $65,000                                      $40,000

  Q1            March       $70,000                                      $40,000

Special ID ABC123 Spent $25K in January, another $15K in February and nothing in March (total $40K)

$ SL Medical Spend = CALCULATE (

SUMX (

FILTER (

SUMMARIZE ( Medical, Medical[special_id], "Aggregate", [$ Medical Spend] ),

[Aggregate] >= [$ SL]

),

[Aggregate]

)

)+0

This isn't working either.  HELP #2!  PLEASE!

I don't know why they aren't working.  Any guidance would be helpful!!!

[Nathaniel_C · Accepted solution]

Hi @novotnajk 
Here is my .pbix https://1drv.ms/u/s!AgCd7AyfqZtE3zw8aPY2ndPJl7a4?e=mmShev 

I 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.
Nathaniel

[novotnajk · Accepted solution]

Hi Nathaniel!

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

[Nathaniel_C · Accepted solution]

Hi @novotnajk 
Here is my .pbix https://1drv.ms/u/s!AgCd7AyfqZtE3zw8aPY2ndPJl7a4?e=mmShev 

I 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.
Nathaniel

[Nathaniel_C]

Hi @novotnajk 
Try this. And I resaved my .pbix, so try clicking on the link again.

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

Per user =
VAR _specialID =
    MAX ( '$ Medical Spend'[Special ID       ] )
VAR _calc =
    CALCULATE (
        [Running Total - ISL],
        FILTER (
            ALL ( '$ Medical Spend'[Special ID       ] ),
            '$ Medical Spend'[Special ID       ] = _specialID
        )
    )
RETURN
    IF ( _calc > 0, _calc, 0 )

[Nathaniel_C]

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]

Hi Nathaniel,

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

It now reads:  

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

[novotnajk]

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.

[Nathaniel_C]

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]

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.

[Nathaniel_C]

Hi @novotnajk , if I understand  your question, no it does not matter, try it and see.
Nathaniel

[Nathaniel_C]

Hi @novotnajk ,
Save your pbix to your one drive, create a link, copy it, and then using the link symbol, here in the interface, paste it.

[novotnajk]

Here's the sample..

https://1drv.ms/u/s!AqGh1etDM9XDigHwuVrosa_U7mT0?e=lNxLMH 

[Nathaniel_C]

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]

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]

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]

I am unable to share the pbix unfortunately because it is tied to millions of health records

[VahidDM]

Hi @novotnajk 

Can you share your PBIX file [after removing sensitive data]?

Appreciate your Kudos!!

[novotnajk]

https://1drv.ms/u/s!AqGh1etDM9XDigHwuVrosa_U7mT0?e=lNxLMH

[novotnajk]

I created a sample PBIX but not sure how to attach.

[novotnajk]

I am unable to share the pbix unfortunately because it is tied to millions of health records in the app