Need help in Planned % Calculation

[Malarvizhi_P_R]

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. 

[Malarvizhi_P_R]

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.

[KarinSzilagyi]

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]

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]

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?

[bhanu_gautam]

@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)