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

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
Anonymous
Not applicable

Cumulative Total and Filter

Hi Community,

 

this is my first post and I have already browsed the web for solutions, but somehow, I am stuck. 😕

So I am trying to show cumulated training completions per date (run-up) in a graph - see below:

When I use the filters on the left, the charts look exactly as I planned to.

 

yojoe_0-1628242751341.png

 

But if I remove the filter and show the "overall cumulations", then the graphs are screwed. 😞

yojoe_1-1628242824154.png

 

 

I am using the following formula to calculate the cumulative completions.

 

Cumulative MEASURE =
VAR LastVisibleDate =
MAX ( 'TKLMSREPORT - 2021-08-02T144129 529'[Completion Date] )
VAR FirstVisibleDate =
MIN ( 'TKLMSREPORT - 2021-08-02T144129 529'[Completion Date] )
VAR LastDateWithSales =
CALCULATE (
MAX ( 'TKLMSREPORT - 2021-08-02T144129 529'[Completion Date] ),
REMOVEFILTERS ('TKLMSREPORT - 2021-08-02T144129 529'[Completion Date])
)
VAR Result =
IF (
FirstVisibleDate <= LastDateWithSales,
CALCULATE (
COUNTA([User ID]),
'TKLMSREPORT - 2021-08-02T144129 529'[Completion Date] <= LastVisibleDate
)
)
RETURN
Result

 

What's wrong? I also found a way to make the overall look good, but then the filter per region does not work:

 

Cumulative MEASURE =
CALCULATE(COUNTA('TKLMSREPORT - 2021-08-02T144129 529'[User ID]),
FILTER (ALLEXCEPT('TKLMSREPORT - 2021-08-02T144129 529', Tabelle1[Title]),
'TKLMSREPORT - 2021-08-02T144129 529'[Completion Date] <= MAX ('TKLMSREPORT - 2021-08-02T144129 529'[Completion Date])
)
)
2 REPLIES 2
Anonymous
Not applicable

Thanks for your response! To be honest, I do not know whether we need to define and ELSE rule. I found nothing about that in my research. I think here, it's more used as a "WHILE"...!?

lbendlin
Super User
Super User

VAR Result =
IF (
FirstVisibleDate <= LastDateWithSales,
CALCULATE (
COUNTA([User ID]),
'TKLMSREPORT - 2021-08-02T144129 529'[Completion Date] <= LastVisibleDate
)
)

 

What should happen if the condition is not met?

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

Top Solution Authors
Top Kudoed Authors