Cumulative calculations from a specific date and normal cumulative on the same page


#1

Hello,
I have a table of pledge information containing a list of donation pledges and amounts still outstanding with many years worth of information.
It is a requirement that on the same page I have two different types of cumulative calculations.
The first is the normal that shows the total pledges up to the date.
The other needs to only show the pledges that have been acquired from July 1 2018. To complicate things, these second calculations also need to be independent of any date slicers chosen so that when the date slicers choose different years, the person can compare the previous years to how those years progressed against 2018 and beyond.

I have tried to use

Total Pledges since July 2018 = 
CALCULATE(
    [Total Pledges],
    FILTER(ALL(Dates),
        Dates[Date] >= DATE(2018, 7, 1)
    )
)

and

Cumulative pledges since July 2018 = 
CALCULATE(
    [Total Pledges since July 2018],
    FILTER(
		ALLSELECTED(Dates),
		Dates[Date] <= MAX( Dates[Date])
	)
)

but these do not give me the desired result.

I have attached a sample Power BI file
Pledges.pbix (414.3 KB)

I am wondering if I need another date table, but not sure how to incorporate that into the model

Any ideas most appreciated.
Thanks


#2

Hi Sam,
I am almost there.
A bit more fiddling and it turned out that I needed to change the filtering on the pledges

Total Pledges since July 2018 = 
CALCULATE( [Total Pledges],
    FILTER(ALL(Pledges[Pledge Date]), 
        Pledges[Pledge Date] >= DATE(2018, 7, 1)  ) )

Similarly

Cumulative pledges since July 2018 = 
CALCULATE( [Total Pledges since July 2018],
    FILTER(ALL(Pledges),
		Pledges[Pledge Date] <= MAX(Pledges[Pledge Date])	)  )

This at least get the cumulative pledges since July 2018.

The problem now is that these do not show when the 2018 and 2019 years are not selected in the year slicer.

Almost…


#3

The best thing to do here is just change the interaction between the filter and your slicer and the chart

image

This is honestly what I would do in this situation. It’s a simple and easy solution.

Chrs
Sam


#4

Hi Sam,
An interesting idea, but since some of the visualisations are cards, this does not work

Sorry


#5

Synergetic, you should be able to turn off the interaction between a slicer and a card visual as well - just be sure check every possible interaction and turn off the ones you don’t want displayed.