Forum Discussion

PabloGiraldo's avatar
PabloGiraldo
Helper IV
5 years ago
Solved

Select specific date measure

I am trying to calculate % Complete of a project based on dates.

 

(Today's date minus Notice to Proceed date)/(Completion Date minus Notice to Proceed Date) 

 

Notice to proceed date is in the start column and Completion date is in the finish date column.

 

pbix attached.

 

Thanks!https://walshgroup-my.sharepoint.com/:u:/p/pgiraldoclavijo/EULCihOzBEZMtFJFyIW_DhsBV2UnsXseGlU3U9PFDVHMWA?e=7jpQ7C 

Need Help
  • dedelman_clng's avatar
    dedelman_clng
    5 years ago

    Hi PabloGiraldo - 

     

    Assuming you want to cap the number at 100%, and assuming your dataset refreshes every day, add this calculated column to the Schedule_BL table.

     

    Pct Complete =
MAX (    //Keeps future projects from showing negative percent
    MIN (   //Keeps completed projects from showing > 100%
        DIVIDE (
            DATEDIFF ( ScheduleBL[(*)Start], TODAY (), DAY ),
            DATEDIFF ( ScheduleBL[(*)Start], ScheduleBL[(*)Finish], DAY ),
            0
        ),
        1
    ),
    0
)

     

    Hope this helps

    David

     

    P.S. - if you are sharing your pbix you may want to reduce the dataset somewhat in order to protect sensitive data. I don't know if any of the data you shared was indeed sensitive, but I will be deleting your PBIX from my computer just in case.

     

2 Replies

  • dedelman_clng's avatar
    dedelman_clng
    Community Champion
    5 years ago

    Hi PabloGiraldo - 

     

    Assuming you want to cap the number at 100%, and assuming your dataset refreshes every day, add this calculated column to the Schedule_BL table.

     

    Pct Complete =
MAX (    //Keeps future projects from showing negative percent
    MIN (   //Keeps completed projects from showing > 100%
        DIVIDE (
            DATEDIFF ( ScheduleBL[(*)Start], TODAY (), DAY ),
            DATEDIFF ( ScheduleBL[(*)Start], ScheduleBL[(*)Finish], DAY ),
            0
        ),
        1
    ),
    0
)

     

    Hope this helps

    David

     

    P.S. - if you are sharing your pbix you may want to reduce the dataset somewhat in order to protect sensitive data. I don't know if any of the data you shared was indeed sensitive, but I will be deleting your PBIX from my computer just in case.