Please help how to show the cumulative completion rate by date?

[bsson82]

Hello Power BI expert,

I have the table like the below.

I would like to show the cumulative completion rate by date for all times when the Status is 'Completed'.

Could you please help me? 

Many thanks.

Item	Status	In Progress	Completed
A	Completed	2020-11-01	2020-11-06
B	Completed	2020-11-01	2020-11-19
C	Completed	2020-11-03	2020-11-22
D	Completed	2020-11-05	2020-11-08
E	Completed	2020-11-01	2020-11-02
F	Completed	2020-11-23	2020-11-30
G	Completed	2020-11-25	2020-11-29
H	Completed	2020-11-10	2020-11-22
I	Completed	2020-11-18	2020-11-26
J	Completed	2020-11-12	2020-11-26
K	In Progress	2020-11-07
L	Not Started
M	In Progress	2020-11-13
N	In Progress	2020-11-06
O	Not Started
P	In Progress	2020-11-02
Q	In Progress	2020-11-28

[amitchandak · Accepted solution]

@bsson82 , Try a measure like

calculate(count(Table[Item]), filter(allselected(Table), Table[Completed] <=max(Table[Completed]) && Table[Status] = "Completed"))

Or with a date table

calculate(count(Table[Item]), filter(allselected(Date), Date[Date] <=max(Date[Date])), Table[Status] = "Completed")

[v-janeyg-msft · Accepted solution]

Hi, @bsson82 

It’s my pleasure to answer for you.

According to your description,I think you can create two measure, one is to calculate the desired result and one is to use it in filter pane to filter status='Completed'.

Like this:

filtercompleted = IF(SELECTEDVALUE('Table'[Completed])=BLANK(),0,1)

percentage = var a=COUNTROWS(ALL('Table'))
var b=CALCULATE(COUNT('Table'[Item]),FILTER(ALL('Table'),[Completed]<=MAX('Table'[Completed])&&[Status]="Completed"))
return DIVIDE(b,a)

Here is my sample .pbix file.Hope it helps.

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

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

[bsson82]

Hello Jeny Guo,

Thats's perfect! it is wokring well.

Thanks and Regards,

Bsson82

[amitchandak · Accepted solution]

@bsson82 , Try a measure like

calculate(count(Table[Item]), filter(allselected(Table), Table[Completed] <=max(Table[Completed]) && Table[Status] = "Completed"))

Or with a date table

calculate(count(Table[Item]), filter(allselected(Date), Date[Date] <=max(Date[Date])), Table[Status] = "Completed")

[bsson82]

Hello,

Thank you so much. it is wokring well.

Thanks and Regards,

bsson82