cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Angel
Resolver III
Resolver III

Difference betweeen two cummulative values

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:

 

DateQuantityCummulative Quantity
01/02/20201515
02/02/2020722
03/02/2020224

 

 

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 valuesDifference
From 01/02/2020 to 02/02/20207
From 01/02/2020 to 03/02/20209
From 02/02/2020 to 03/02/20202

 

Can anyone help me, please?..

 

Thanks in advance,

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

Demoted CQ.JPG

 

 

 

 

 

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

Cumul Qty.JPG

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

 

 

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

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

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.

Demoted CQ.JPG

 

 

 

 

 

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

Cumul Qty.JPG

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

 

 

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
Not applicable

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

harshnathani
Community Champion
Community Champion

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.
 
Slicer.PNG
 
Regards,
Harsh Nathani
 
Did I answer your question? Mark my post as a solution! and give Kudos

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors