Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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 |
Can anyone help me, please?..
Thanks in advance,
Solved! Go to 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.
@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
// 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.
@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
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
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
D
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)
User | Count |
---|---|
20 | |
18 | |
17 | |
11 | |
7 |
User | Count |
---|---|
28 | |
27 | |
13 | |
12 | |
12 |