Difference betweeen two cummulative values

[Angel]

Hi, everyone

I have been struggled with the phormula to get a difference between two cummulative values and I'm stuck. I want to show in a card the difference between two cummulative values depending on the dates selected by slicers.

Data structure:

Date	Quantity	Cummulative Quantity
01/02/2020	15	15
02/02/2020	7	22
03/02/2020	2	24

I have created a measure to get the cummulative value:

Cummulative Quantity = 

CALCULATE(SUM(Sales[Quantity]);FILTER(ALL(Calendar); Calendar[Date] <= MAXX(Calendar;Calendar[Date])))

The goal is showing the cummulative difference in a card depending on a date slicer. Examples:

Date slicer values	Difference
From 01/02/2020 to 02/02/2020	7
From 01/02/2020 to 03/02/2020	9
From 02/02/2020 to 03/02/2020	2

Can anyone help me, please?..

Thanks in advance,

[Anonymous]

// Calendar must be marked as the Date table
// in the model.

[Cumul Qty] = // that's faster and simpler
	CALCULATE(
		SUM( Sales[Quantity] ),
		Calendar[Date] <= MAX( Calendar[Date] )
	)

[Diff] =
	[Cumul Qty]
	- CALCULATE(
		[Cumul Qty],
		FIRSTDATE( Calendar[Date] )
	)

Best

D

[Angel · Accepted solution]

Hi, @harshnathani , @Anonymous 

First of all, thanks for the quick responses. It have been useful. 

@harshnathani. I tried phormula you said and it shows an error. I think it's because Cummulative Quantity is a measure in the model, instead of a colum.

@Anonymous. The phormula you said works, except for the phormula to calculate 'Cumul Qty'. 

So, I have changed phormula por Cumul Qty and it works.

Cummulative Quantity = 

CALCULATE(SUM(Sales[Quantity]);FILTER(ALL('Calendar'); 'Calendar'[Date] <= MAXX('Calendar';'Calendar'[Date])))

Diff = 
	[Cummulative Quantity]
	- CALCULATE(
		[Cummulative Quantity];
		FIRSTDATE( 'Calendar'[Date] )
	)

And it works.....

Thanks

[harshnathani]

Hi @Angel ,

Thanks for replying back.

Yes, for my solution to work, Cumulative Quantity has to be a column in the Table. 🙂

Thanks,

Harsh Nathani

[Anonymous]

This is what your measure should be:

[Cumul Qty] = // that's faster and simpler
var __lastVisibleDate = MAX( Calendar[Date] )
return
	CALCULATE(
		SUM( Sales[Quantity] ),
		Calendar[Date] <= __lastVisibleDate
	)

My prev version returned an error because you cannot use the shortened syntax for filters when there's a function involved. The above version is the best you can have.

Best

D 

[harshnathani]

Hi @Angel ,

1. Create a Calculated Column (this is to get the Cumulative Totals of the Previous Date)

Demoted CQ =
VAR maxi =
CALCULATE (
MAX ( Data1[Cummulative Quantity] ),
FILTER ( ALL ( Data1 ), Data1[Date] < EARLIER ( Data1[Date] ) )
)
VAR mini =
CALCULATE (
MIN ( Data1[Cummulative Quantity] ),
FILTER ( ALL ( Data1 ), Data1[Date] = MIN ( Data1[Date] ) )
)
RETURN
IF ( Data1[Date] = MIN ( Data1[Date] ), mini, maxi )

2. Create a Calculated Column (This Calculates the Difference between the Cumulative Totals)

Difference = Data1[Cummulative Quantity] -Data1[Demoted CQ]

3. Drag your Slicer and Card . Slicer will have the Dates and Card the Difference.

Regards,

Harsh Nathani

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