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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors