Cumulative Totals Including Non-Filtered Values

[novotnajk]

Hi Everyone!  So close to having this figured out!

See the screenshot below.

Here are the formulas that I have:

$ 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

$ Cumulative SL Medical Spend =

VAR _CurrentDate = MAX('Reporting'[reporting_date])

VAR _Calc = CALCULATE([$ SL Medical Spend],FILTER(ALL('Reporting'[reporting_date]),'Reporting'[reporting_date] <=_CurrentDate))

return _Calc

Both of these formulas work just fine when I do not apply a filter slicer on the page (see the image Reporting Date in the upper right hand corner).

When I apply this filter slicer and select Q4 2020 to Q3 2021, the October figure includes all of the spend in the unfiltered months.  Essentially what I'd like to see is the October 2020 value of $8,252,120 be reduced by the cumulative spend in the prior month (September 2020 of $7,001,979) for an October spend of $1,250,141.  Then the subsequent months after October 2020 would be cumulative going forward.

Once I figure this out, I can apply the same logic to the other cumulative measure.

Thanks for your help!

[Fowmy · Accepted solution]

@novotnajk 

Use ALLSELECTED.

$ Cumulative Medical Spend =
VAR _CurrentDate =
    MAX ( 'Reporting'[reporting_date] )
VAR _Calc =
    CALCULATE (
        [$ Medical Spend],
        FILTER (
            ALLSELECTED ('Reporting'),
            'Reporting'[reporting_date] <= _CurrentDate
        )
    )
RETURN
    _Calc

$ Cumulative SL Medical Spend =
VAR _CurrentDate =
    MAX ( 'Reporting'[reporting_date] )
VAR _Calc =
    CALCULATE (
        [$ SL Medical Spend],
        FILTER (
            ALLSELECTED (  'Reporting' ),
            'Reporting'[reporting_date] <= _CurrentDate
        )
    )
RETURN
    _Calc

[Nathaniel_C · Accepted solution]

Hi @novotnajk 
Here you go.

Running Total = 
var _minDateCalc = CALCULATE(MIN(MedicalSpend[Reporting Date]),ALLSELECTED()) // This uses ALLSELECTED() and thus gets the minimum date based on the slicer



VAR _beginningDate =
   MIN ( MedicalSpend[Reporting Date] )
VAR _currentDate =
    MAX ( MedicalSpend[Reporting Date] )
Var _specialID = MAX(MedicalSpend[Special ID])    
VAR _calc =
    CALCULATE (
        [Sum of Medical Spend],
                  ALL(MedicalSpend),
            
                MedicalSpend[Reporting Date] <= _currentDate && MedicalSpend[Reporting Date]>=   _minDateCalc,
                MedicalSpend[Special ID]=_specialID
        )
      
RETURN _calc+0

It was the beginning date that was the problem. The minimum date that  was pulling before, was the minimum date on each row, which is why it did not total.  The new var calculates the minimum date from the table based on the slicer because we use ALLSELECTED() and calculate it before we do the main calculation.  

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

[Fowmy · Accepted solution]

@novotnajk 

Use ALLSELECTED.

$ Cumulative Medical Spend =
VAR _CurrentDate =
    MAX ( 'Reporting'[reporting_date] )
VAR _Calc =
    CALCULATE (
        [$ Medical Spend],
        FILTER (
            ALLSELECTED ('Reporting'),
            'Reporting'[reporting_date] <= _CurrentDate
        )
    )
RETURN
    _Calc

$ Cumulative SL Medical Spend =
VAR _CurrentDate =
    MAX ( 'Reporting'[reporting_date] )
VAR _Calc =
    CALCULATE (
        [$ SL Medical Spend],
        FILTER (
            ALLSELECTED (  'Reporting' ),
            'Reporting'[reporting_date] <= _CurrentDate
        )
    )
RETURN
    _Calc

[novotnajk]

That didn't work.  The values stayed the same.  😞

Should I change anything to the underlying measures within each? 

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

$ SL Medical Spend =

CALCULATE (

SUMX (

FILTER (

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

[Aggregate] >= [$ SL]

),

[Aggregate]

)

)+0

[Fowmy]

@novotnajk 

This is a new calculaton. what happened to the one you wanted to resolve in your first question?
Please share sample data and the expected results or show exactly what sort of problems you are facing in the calculation.

[novotnajk]

There aren't any new calculations.  The new ones that I posted are included in the original calculations as a measure.  I changed the ALLSELECTED as recommended.  Within this calculation is another measure called $ Medical Spend (and the other is $ SL Medical Spend).

The problem is that when I use the slicer to filter dates, the data from unfiltered spend is still part of the calculation and is carried over.  Does that make sense?