Forum Discussion

PabloGiraldo's avatar
PabloGiraldo
Icon for Helper IV rankHelper 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 s...
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.

     

dedelman_clng's avatar
dedelman_clng
Icon for Community Champion rankCommunity 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.