Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
novotnajk
Resolver I
Resolver I

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!Untitled.png
2 ACCEPTED SOLUTIONS
Fowmy
Super User
Super User

@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



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

Nathaniel_C
Super User
Super User

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

ISL reWork.PNG
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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

5 REPLIES 5
Nathaniel_C
Super User
Super User

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

ISL reWork.PNG
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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Fowmy
Super User
Super User

@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



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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

@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.

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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?

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors