Forum Discussion

Malarvizhi_P_R's avatar
Malarvizhi_P_R
Regular Visitor
10 months ago
Solved

Need help in Planned % Calculation

Requirement - Need to calculate planned %

Calculation ask - Need to consider Target start and Target end date columns for calculating date difference with day count.
If 10 days is the difference of target start and Target End. 5 days completed then 50% done should be given as planned %.

Note: Both Date columns has value from 2018 to 2027 

How to implement this logic using DAX. 

  • bhanu_gautam's avatar
    bhanu_gautam
    10 months ago

    Malarvizhi_P_R You can implement this logic in DAX by calculating the total planned duration (in days) and the elapsed duration (in days), then dividing the elapsed by the total to get the planned percentage.

     

    dax
    Planned % =
    VAR TodayDate = TODAY()
    VAR StartDate = [Target Start]
    VAR EndDate = [Target End]
    VAR TotalDays = DATEDIFF(StartDate, EndDate, DAY) + 1
    VAR ElapsedDays =
    IF(
    TodayDate < StartDate,
    0,
    IF(
    TodayDate > EndDate,
    TotalDays,
    DATEDIFF(StartDate, TodayDate, DAY) + 1
    )
    )
    RETURN
    DIVIDE(ElapsedDays, TotalDays, 0)

  • KarinSzilagyi's avatar
    KarinSzilagyi
    10 months ago

    Hi Malarvizhi_P_R could you share an example of your raw data? 
    I've tried to recreate a dataset based on what I can tell from your image, but my results don't look anywhere close to yours.

    This is how I assume your data might look like based on that image:

    And this is my result when I place it in a Matrix with the 0-100% Version of the Formula:

    Could you provide an Excel with data similar to your specific case?

     

5 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    10 months ago

    Malarvizhi_P_R You can implement this logic in DAX by calculating the total planned duration (in days) and the elapsed duration (in days), then dividing the elapsed by the total to get the planned percentage.

     

    dax
    Planned % =
    VAR TodayDate = TODAY()
    VAR StartDate = [Target Start]
    VAR EndDate = [Target End]
    VAR TotalDays = DATEDIFF(StartDate, EndDate, DAY) + 1
    VAR ElapsedDays =
    IF(
    TodayDate < StartDate,
    0,
    IF(
    TodayDate > EndDate,
    TotalDays,
    DATEDIFF(StartDate, TodayDate, DAY) + 1
    )
    )
    RETURN
    DIVIDE(ElapsedDays, TotalDays, 0)

  • KarinSzilagyi's avatar
    KarinSzilagyi
    Super User
    10 months ago

    Hi Malarvizhi_P_R, I'm not entirely sure whether I understood your request correctly but did you mean something like this:

    Planned % = 
VAR StartDate = SELECTEDVALUE(DimProjects[TargetStart])
VAR EndDate   = SELECTEDVALUE(DimProjects[TargetEnd])
VAR TodayDate = TODAY()

VAR InvalidDates = ISBLANK(StartDate) || ISBLANK(EndDate) || EndDate <= StartDate

VAR TotalDays = DATEDIFF(StartDate, EndDate, DAY)

VAR ElapsedDaysRaw = DATEDIFF(StartDate, TodayDate, DAY)

VAR ElapsedDays =  MAX(0, MIN(ElapsedDaysRaw, TotalDays))

RETURN
IF(
    InvalidDates,
    BLANK(),
    DIVIDE(ElapsedDays, TotalDays)
)

    I assumed that you meant to calculate the percentage of how far along you are today compared to the Start- and EndDate and also added a check whether the Start- vs EndDate are valid or not. If you meant to check based on a third column that has the current status-date rather than using today as a reference-date you just need to replace the Today() with the selection for the column you want to use => then I would recommend to add a fallback in case that column is blank though, e.g. add another " || ISBLANK(TodayDate)" or use today() in the definition of the TodayDate-variable.

    Btw this bit:

    VAR ElapsedDays =  MAX(0, MIN(ElapsedDaysRaw, TotalDays))

     is just to ensure that the percentage stays between 0 - 100%. If you want to get negative percentages (e.g. current date is before the official project StartDate) or want to show >100% (e.g. currently 260% for Project 10) you just have to replace this part:

    RETURN
IF(
    InvalidDates,
    BLANK(),
    DIVIDE(ElapsedDays, TotalDays)
)

    with: 

    RETURN
IF(
    InvalidDates,
    BLANK(),
    DIVIDE(ElapsedDaysRaw, TotalDays)
)

    which will give you:

     

    • Malarvizhi_P_R's avatar
      Malarvizhi_P_R
      Regular Visitor
      10 months ago

      Hello KarinSzilagyi . Thank you for the input. But the output that we are getting is not working as we expected. The expected output and the output we are getting is given in the below screenshot .

      Note: It is a matrix view with 2 levels/hierarchy of row values and 1 level of Column value and others are values
      Expected out:


      Output we got from the code

       

       

      • KarinSzilagyi's avatar
        KarinSzilagyi
        Super User
        10 months ago

        Hi Malarvizhi_P_R could you share an example of your raw data? 
        I've tried to recreate a dataset based on what I can tell from your image, but my results don't look anywhere close to yours.

        This is how I assume your data might look like based on that image:

        And this is my result when I place it in a Matrix with the 0-100% Version of the Formula:

        Could you provide an Excel with data similar to your specific case?

         

  • Malarvizhi_P_R's avatar
    Malarvizhi_P_R
    Regular Visitor
    10 months ago

    It is working but when Targat end date is greater than today's date. It shouldn't populate 100 % instead need to calculate 30%,20 % based on Target start date. Currently the code is populating 100%. for the above mentioned senario. Could you please help me on this.