Scheduled vs Completed

[ajbogle]

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

1. Total Installs Scheduled = CALCULATE(COUNTA('Installs'[Current Installation - Scheduled]))
2. Total Installs Completed = CALCULATE(COUNTA('Installs'[Current Installation - Complete Timestamp]))

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.

[ajbogle]

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

[v-xiaotang]

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.

[ajbogle]

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.

[v-xiaotang · Accepted 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.

[ajbogle]

Brilliant!

[v-xiaotang]

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.