cancel
Showing results 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.

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.

2 ACCEPTED SOLUTIONS
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
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``````

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

Proud to be a Super User!

5 REPLIES 5
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``````

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

Proud to be a 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
Resolver I

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
Super User

@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
Resolver I

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?

Announcements

#### 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

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors