cancel
Showing results for
Did you mean:
Helper I

## Cumulative Completion Rate with Month Slicer

Hi everyone,

I'm trying to calculate cumulative completion rate by all users over moths, the issue is that in the below table for ex when I filter on october it divides users who finished till october / all users except those who finished in November.

I have a dim_date table which is connect to the data table, the retaltion is between Date from dim_date and Completion Date from Data table
Also in dim date table im numbering the months 1,2,3,4 etc

 ID Completion_status Completion Date 1 0 2 0 3 0 4 0 5 0 6 1 11/1/2022 7 1 11/1/2022 8 1 11/1/2022 9 1 11/2/2022 10 1 11/1/2022 11 1 11/6/2022 12 1 11/4/2022 13 1 11/2/2022 14 1 10/13/2022 15 1 10/14/2022 16 1 10/14/2022 17 1 10/13/2022 18 1 10/15/2022 19 1 10/13/2022 20 1 10/13/2022 21 1 10/13/2022 22 1 10/13/2022 23 1 10/18/2022 24 1 10/13/2022 25 1 10/13/2022 26 1 10/13/2022 27 1 10/13/2022 28 1 9/10/2022 29 1 9/8/2022

the formula I use
Completion% =
VAR comp rate = SUM(Table[completion_status]) / count(Table[ID])
Return

CALCULATE(Table[Completion%],filter(ALL(Dim_Date),Dim_Date[Month Number] <= MAX(Dim_Date[Month Number])))

the expected result when I filter
on september is 2/29 = 7%
on october is 16/29 = 55%
on November is 24/29 = 83%

1 ACCEPTED SOLUTION
Resident Rockstar

Hi @Mourt

Please find below an appraoch:

1) base table (completion status is type "whole number")

2) Data model

3) Measure and result

Completion% =

var var_RollingCompletion =
CALCULATE(
SUM(CompletionRate[Completion_status]),
ALL(DimDate),
DimDate[Date]<= MAX(DimDate[Date])
)

var var_AllItems =
CALCULATE(
COUNTROWS(CompletionRate),
ALL(CompletionRate)
)

RETURN
DIVIDE(var_RollingCompletion, var_AllItems, BLANK())

Best regards

Michael

-----------------------------------------------------

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!
3 REPLIES 3
Resident Rockstar

Hi @Mourt

Please find below an appraoch:

1) base table (completion status is type "whole number")

2) Data model

3) Measure and result

Completion% =

var var_RollingCompletion =
CALCULATE(
SUM(CompletionRate[Completion_status]),
ALL(DimDate),
DimDate[Date]<= MAX(DimDate[Date])
)

var var_AllItems =
CALCULATE(
COUNTROWS(CompletionRate),
ALL(CompletionRate)
)

RETURN
DIVIDE(var_RollingCompletion, var_AllItems, BLANK())

Best regards

Michael

-----------------------------------------------------

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!
Helper I

Thanks for the solution, works perfectly, just did a slight modification as Users belong to different departments

Completion% =

var var_RollingCompletion =
CALCULATE(
SUM(CompletionRate[Completion_status]),
ALL(DimDate),
DimDate[Date]<= MAX(DimDate[Date])
)

var var_AllItems =
CALCULATE(
COUNTROWS(CompletionRate),
ALL(CompletionRate)
)

RETURN
DIVIDE(var_RollingCompletion, var_AllItems, BLANK())
Resident Rockstar

@Mourt  Aweseom! Thank you for the feedback! 🙂

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

Announcements

#### Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

#### Power BI March 2023 Update

Find out more about the March 2023 update.

#### March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors