Forum Discussion

Malarvizhi_P_R's avatar
Malarvizhi_P_R
Regular Visitor
11 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 ...
  • bhanu_gautam's avatar
    bhanu_gautam
    11 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
    11 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?

     

KarinSzilagyi's avatar
KarinSzilagyi
Icon for Super User rankSuper User
11 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: