Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi,
I am using two parameters: year and month.
After selecting year and month the graph shows [Total Quantity] for all data up to the choosen year and month. It works perfectly with [Total Quantity]. But not as perfect with [Cummulative Total Quantity (P)].
As shown on the graph below, the cummulative total quantity measure works as intended, it calculates the cummulative up to the choosen month/year but after, it show constant line. How to change this measure to get rid off this constant line?
The measure [Total Quantity Cummulative (P)] as follows:
Cummulative Total Quantity (P) =
VAR ChoosenYearTest = [Selected Year Value]
VAR ChoosenMonthTest = [Selected Month Value]
RETURN
CALCULATE(
[Total Quantity],
FILTER(
ALL('Calendar'),
(
'Calendar'[Year_ID] < ChoosenYearTest ||
(
'Calendar'[Year_ID] = ChoosenYearTest &&
'Calendar'[Month_ID] <= ChoosenMonthTest
)
) &&
'Calendar'[Transaction_Date] <= MAX('Calendar'[Transaction_Date])
)
)
File:
https://drive.google.com/file/d/1Nhn5iP5KhCe_A0KchZCdOJq5r82HcZFH/view?usp=sharing
Thank You.
Solved! Go to Solution.
Cummulative Total Quantity (P) =
VAR ChoosenYearTest = [Selected Year Value]
VAR ChoosenMonthTest = [Selected Month Value]
RETURN
IF (
MAX('Calendar'[Year_ID]) < ChoosenYearTest ||
(
MAX('Calendar'[Year_ID]) = ChoosenYearTest &&
MAX('Calendar'[Month_ID]) <= ChoosenMonthTest
),
CALCULATE(
[Total Quantity],
FILTER(
ALL('Calendar'),
(
'Calendar'[Year_ID] < ChoosenYearTest ||
(
'Calendar'[Year_ID] = ChoosenYearTest &&
'Calendar'[Month_ID] <= ChoosenMonthTest
)
) &&
'Calendar'[Transaction_Date] <= MAX('Calendar'[Transaction_Date])
)
),
BLANK()
)
Cummulative Total Quantity (P) =
VAR ChoosenYearTest = [Selected Year Value]
VAR ChoosenMonthTest = [Selected Month Value]
RETURN
IF (
MAX('Calendar'[Year_ID]) < ChoosenYearTest ||
(
MAX('Calendar'[Year_ID]) = ChoosenYearTest &&
MAX('Calendar'[Month_ID]) <= ChoosenMonthTest
),
CALCULATE(
[Total Quantity],
FILTER(
ALL('Calendar'),
(
'Calendar'[Year_ID] < ChoosenYearTest ||
(
'Calendar'[Year_ID] = ChoosenYearTest &&
'Calendar'[Month_ID] <= ChoosenMonthTest
)
) &&
'Calendar'[Transaction_Date] <= MAX('Calendar'[Transaction_Date])
)
),
BLANK()
)
Hi,
Please try something like below if it suits your requirement.
Cummulative Total Quantity (P) =
VAR ChoosenYearTest = [Selected Year Value]
VAR ChoosenMonthTest = [Selected Month Value]
RETURN
IF (
NOT ISBLANK ( [Total Quantity] ),
CALCULATE (
[Total Quantity],
FILTER (
ALL ( 'Calendar' ),
( 'Calendar'[Year_ID] < ChoosenYearTest
|| ( 'Calendar'[Year_ID] = ChoosenYearTest
&& 'Calendar'[Month_ID] <= ChoosenMonthTest ) )
&& 'Calendar'[Transaction_Date] <= MAX ( 'Calendar'[Transaction_Date] )
)
)
)
Unfortunately, not working.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
8 | |
7 | |
6 | |
6 |
User | Count |
---|---|
28 | |
11 | |
11 | |
10 | |
6 |