Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi Everyone,
I can't figure this out for the life of me, but what I am looking to achieve seems rather basic. I have a table called 'Installs' that has a unique key called 'RID'. Additionally, there are two dates, Scheduled and Completed.
I'm looking to get a count of Scheduled and a count of Completed, however, I want the completed count to only count rows that were done within the scheduled month. For example:
The left date is the scheduled date, and the 'Scheduled' column is a measure and 'Completed' is a measure
However, if I create a new table with the rows being the Scheduled date and the columns being the Completed date, I get the following:
As you can see the real completed count is 98 out of 129 scheduled for January, or 81 completed out of 116 for February.
Solved! Go to Solution.
Hi @ajbogle
Thanks for your reply.
You can try this, for more please see sample file attached below.
Schedule = SUMX(FILTER(ALL('Table'),'Table'[S]=MIN('Table'[S])),[Value])+3
Completed = SUMX(FILTER(ALL('Table'),'Table'[S]=MIN('Table'[S]) && 'Table'[C]=MIN('Table'[S])),[Value])
Realization Rate = DIVIDE([Completed],[Schedule])
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
@v-xiaotang - Thanks for replying.
The expected outcome is the bottom table. I want a single column to count the number of completed jobs within a scheduled period.
Desired Results:
Month Year | Scheduled | Completed | Realization Rate |
January 2022 | 129 | 98 | 76% |
February 2022 | 116 | 81 | 70% |
March 2022 | 158 | 129 | 82% |
Hi @ajbogle
Thanks for your reply!
For Scheduled date = Jan 22, total = 98+19+4+4+1= 126,
For Scheduled date = Feb 22, total = 81+16+14+2= 113,
but in your Desired Results, they all added three. Does this mean that we need to add 3 fixedly?
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Correct, so I'd like to create a measure that only shows how many were completed within the Schedule Month.
Scheduled measure is correct, but my Completed measure counts all the completed but I want to filter it so it only counts the completed within the month.
Hi @ajbogle
Thanks for your reply.
You can try this, for more please see sample file attached below.
Schedule = SUMX(FILTER(ALL('Table'),'Table'[S]=MIN('Table'[S])),[Value])+3
Completed = SUMX(FILTER(ALL('Table'),'Table'[S]=MIN('Table'[S]) && 'Table'[C]=MIN('Table'[S])),[Value])
Realization Rate = DIVIDE([Completed],[Schedule])
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Brilliant!
Hi @ajbogle
Thanks for reaching out to us.
>>As you can see the real completed count is 98 out of 129 scheduled for January, or 81 completed out of 116 for February.
So what's the expected outcome?
For row=Jan 22, col= Jan 22, value=129?
For row=Jan 22, col= Feb 22, value=?
For row=Jan 22, col= Mar 22, value=?
...
For row=Feb 22, col= Jan 22, value= null?
For row=Feb 22, col= Feb 22, value= 116?
...
.
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
90 | |
89 | |
79 | |
70 | |
68 |
User | Count |
---|---|
222 | |
128 | |
117 | |
82 | |
77 |