Forum Discussion

novotnajk's avatar
novotnajk
Resolver I
4 years ago
Solved

Cumulative Totals Including Non-Filtered Values

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!
Need Help
  • Fowmy's avatar
    Fowmy
    4 years ago

    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's avatar
    Nathaniel_C
    4 years ago

    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

5 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    4 years ago

    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's avatar
      novotnajk
      Resolver I
      4 years ago

      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's avatar
        Fowmy
        Super User
        4 years ago

        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.

  • Nathaniel_C's avatar
    Nathaniel_C
    Community Champion
    4 years ago

    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