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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
sparker22
Frequent Visitor

Cumulative count based on relative date filter

I have a graph that using a relative date filter looks at the last 12 months.

Is it possible to create a cumulative count that uses the same relative date filter, rather than including previous month totals or using a fixed date filter which will require changing each month?

i.e. not this 

sparker22_0-1673441958549.png

 

1 ACCEPTED SOLUTION

@sparker22 

Please try

Cumulative Total =
VAR CurrentDate =
MAX ( 'Calendar'[Date] )
VAR FirstDateWithData =
CALCULATE ( MIN ( 'Outcome'[Date] ), REMOVEFILTERS () )
RETURN
CALCULATE (
COUNT ( 'Outcome'[OutcomeID] ),
'Calendar'[Date] >= FirstDateWithData,
'Calendar'[Date] <= CurrentDate
)

View solution in original post

8 REPLIES 8
tamerj1
Super User
Super User

Hi @sparker22 
Please try

Cumulative Measure 2 =
IF (
    NOT ISEMPTY ( FactTable ),
    [Cumulative Measure]
)

Hi @tamerj1 

This succeeds in removing the line on the graph on irrelevant months, but still totals the previous irrelevant months figures in the first relevant month

sparker22_0-1673444903135.png

 

Hi @sparker22 

In this case you need to edit the original measure code. Can you paste the code in a reply?

This is the original code. The relative date filter is added on the visual level, rather than code.

 

Cumulative Total =
CALCULATE (
COUNT('Outcome'[OutcomeID]),
FILTER (
ALL( 'Calendar'[Date] ),
'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
)
)

Thanks, this worked!!

@sparker22 

Please try

Cumulative Total =
VAR CurrentDate =
MAX ( 'Calendar'[Date] )
VAR FirstDateWithData =
CALCULATE ( MIN ( 'Outcome'[Date] ), REMOVEFILTERS () )
RETURN
CALCULATE (
COUNT ( 'Outcome'[OutcomeID] ),
'Calendar'[Date] >= FirstDateWithData,
'Calendar'[Date] <= CurrentDate
)

FreemanZ
Super User
Super User

Dhi @sparker22 ,

try to write a measure like:

CumCount =

VAR _date = MAX(TableName[Date])

RETURN

CALCULATE(

    COUNT(TableName[ColumnOfInterest]),

    TableName[Date] <= _date

)

Hi @FreemanZ 

Appears to be counting backwards, and still including totals outside the relative date range

sparker22_0-1673444303617.png

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.