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.
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 |
Solved! Go to 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")
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.
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.
Hello Jeny Guo,
Thats's perfect! it is wokring well.
Thanks and Regards,
Bsson82
@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")
Hello,
Thank you so much. it is wokring well.
Thanks and Regards,
bsson82
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |