Cumulative Completion Rate with Month Slicer

[Mourt]

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%

[Mikelytics · Accepted solution]

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.

[Mourt]

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

[Mikelytics]

@Mourt  Aweseom! Thank you for the feedback! 🙂