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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors