cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors