Forum Discussion

Ykankam's avatar
Ykankam
Helper I
4 years ago
Solved

undefined

How to calculate project progress using start date and end date

Need Help
Show and Tell
Tips and Tricks
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    Hi, Ykankam 

    Please try formulas like:

    Percentage% =
VAR a =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER ( 'Table', 'Table'[Project] = EARLIER ( 'Table'[Project] ) )
    )
VAR b =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            'Table',
            'Table'[Project] = EARLIER ( 'Table'[Project] )
                && 'Table'[Due Status] = "Not Overdue"
        )
    )
RETURN
    b / a

    or measure

    Percentage1% = 
VAR a =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER ( ALLSELECTED('Table'), 'Table'[Project] = max( 'Table'[Project] ) )
    )
VAR b =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALLSELECTED('Table'),
            'Table'[Project] = MAX( 'Table'[Project] )
                && 'Table'[Due Status] = "Not Overdue"
        )
    )
RETURN
    b/a+0

    If it doesn't work, please share a sample file and expected result for further research.

    Best Regards,
    Community Support Team _ Eason

     

4 Replies

  • Ykankam's avatar
    Ykankam
    Helper I
    4 years ago

    From the image above. I have a list of projects with start date and end date. Each project has a status.

    Now I want a column that will calculate the aggregated completion rate(percentage) for each project base on the start date and end date.

    • v-easonf-msft's avatar
      v-easonf-msft
      Community Support
      4 years ago

      Hi, Ykankam 

      Please try formulas like:

      Percentage% =
VAR a =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER ( 'Table', 'Table'[Project] = EARLIER ( 'Table'[Project] ) )
    )
VAR b =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            'Table',
            'Table'[Project] = EARLIER ( 'Table'[Project] )
                && 'Table'[Due Status] = "Not Overdue"
        )
    )
RETURN
    b / a

      or measure

      Percentage1% = 
VAR a =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER ( ALLSELECTED('Table'), 'Table'[Project] = max( 'Table'[Project] ) )
    )
VAR b =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALLSELECTED('Table'),
            'Table'[Project] = MAX( 'Table'[Project] )
                && 'Table'[Due Status] = "Not Overdue"
        )
    )
RETURN
    b/a+0

      If it doesn't work, please share a sample file and expected result for further research.

      Best Regards,
      Community Support Team _ Eason

       

      • Ykankam's avatar
        Ykankam
        Helper I
        4 years ago

        v-easonf-msft VahidDM  thank you both. It worked.

         

        How do create a dax formula to show all projects startinng next month and another one to show all projects ending this month?