cancel
Showing results 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

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:

 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

1 ACCEPTED SOLUTION
Resolver III

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

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

Resolver III

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

Community Champion

Hi @Angel ,

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

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.

Regards,
Harsh Nathani

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

Announcements

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

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### 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
Top Kudoed Authors