Forum Discussion

ajbogle's avatar
ajbogle
Icon for Helper I rankHelper I
4 years ago
Solved

Scheduled vs Completed

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.

 

 

 

Need Help
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    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.

6 Replies

  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    4 years ago

    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.

  • ajbogle's avatar
    ajbogle
    Icon for Helper I rankHelper I
    4 years ago

    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 YearScheduledCompletedRealization Rate
    January 20221299876%
    February 20221168170%
    March 202215812982%
    • v-xiaotang's avatar
      v-xiaotang
      Icon for Community Support rankCommunity Support
      4 years ago

      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's avatar
        ajbogle
        Icon for Helper I rankHelper I
        4 years ago

        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.