cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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

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!

------------------------------------------------------------------
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

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!

------------------------------------------------------------------
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

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors