How to calculate the estimate hour per day per project with realized hours in the same visual?

[Anthony_2022]

I try to present the realized hours versus the planned hours for several projects in the same graph.

I've done a lot of research, the answers revolve around creating a calculate column to get the estimated hours. This always answers the need of the original question. However, in my case, it can't work since I have several projects, therefore several estimates. I don't see how to procedurally create these calculated columns. (In my real data, I have several hundred projects)

I am able to get the expected number of hours for each day. However, the planned hours stop where the last hour entered is.

I suspect that this is a matter of filters or assigning a new date table to the DAX formula.

I have another table that consists of a table of dates. The relationship between the two tables is on the date column.

Data sample :

Dates | Project Hours | End_Date | Total_Estimate_Hours

2022-02-01	Project A	1	2022-02-28	50
2022-02-02	Project A	1	2022-02-28	50
2022-02-03	Project A	1	2022-02-28	50
2022-02-04	Project A	1	2022-02-28	50
2022-02-05	Project A	1	2022-02-28	50
2022-02-06	Project A	1	2022-02-28	50
2022-02-07	Project A	1	2022-02-28	50
2022-02-08	Project A	1	2022-02-28	50
2022-02-09	Project A	1	2022-02-28	50
2022-02-10	Project A	1	2022-02-28	50
2022-02-11	Project A	1	2022-02-28	50
2022-02-12	Project A	1	2022-02-28	50
2022-02-13	Project A	3	2022-02-28	50
2022-02-14	Project A	8	2022-02-28	50
2022-02-15	Project A	1	2022-02-28	50
2022-02-16	Project A	1	2022-02-28	50
2022-02-17	Project A	1	2022-02-28	50
2022-02-18	Project A	1	2022-02-28	50
2022-02-01	Project B	2	2022-02-26	28
2022-02-03	Project B	3	2022-02-26	28
2022-02-04	Project B	2	2022-02-26	28
2022-02-09	Project B	2	2022-02-26	28
2022-02-10	Project B	5	2022-02-26	28
2022-02-12	Project B	3	2022-02-26	28
2022-02-13	Project B	4	2022-02-26	28
2022-02-16	Project B	2	2022-02-26	28

[AUaero]

I don't really understand your sample data vs the screenshots you posted.  It looks like the actual and estimated hours in your graphs are cumulative sums.  Can you post the raw data for hours and estimates for a couple of projects along with your measure defintions?

[Anthony_2022]

I'm sorry, I forgot to put the DAX formulas. I hadn't included them since I intended to add a PBIX file, but I couldn't find a way to attach it to the post.

Here is the DAX formula for realized hours:

Realized hours = 
VAR LastVisibleDate = MAX(Dates[Date])
Var FirstVisibleDate = MIN(Dates[Date])
Var LastDateWithHours = CALCULATE(MAX('Table'[Dates]),REMOVEFILTERS())
Var Result = IF(FirstVisibleDate <= LastDateWithHours, CALCULATE(SUM('Table'[Hours]),dates[date] <= LastVisibleDate))
return Result

Here is the DAX formula for estimated hours:

Estimate Hours = 
VAR LastVisibleDate = MAX('Table'[End Date])
Var FirstVisibleDate = MIN(Dates[Date])
VAR DayBetweenDates = DATEDIFF(FirstVisibleDate,LastVisibleDate,DAY)

return SELECTEDVALUE('Table'[Total Estimate Hours])/DayBetweenDates

The sample data are identical to my source data, they have been ultra simplified to facilitate finding a solution.

The hours data have been aggregated for performance reasons. Initially, it was a table with multiple rows per day per user per project.

The projects data was a table by itself. It was combined with the previous table to facilitate the application of filters and measurements. This seemed to be the best decision in terms of performance, but I haven't measured it until now because of the concern initially presented.

[v-zhangti]

Hi, @Anthony_2022 

What kind of results do you expect? I have made some adjustments, please check if it meets your needs.

Realized hours = 
VAR LastVisibleDate = SELECTEDVALUE('Table'[Date])
Var FirstVisibleDate = CALCULATE(MIN('Table'[Date]),FILTER(ALL('Table'),[Project]=MAX('Table'[Project])))
Var LastDateWithHours = CALCULATE(MAX('Table'[Date]),REMOVEFILTERS())
Var Result = IF(FirstVisibleDate <= LastDateWithHours, CALCULATE(SUM('Table'[Hours]),FILTER(ALL('Table'),'Table'[Date] <=LastVisibleDate&&[Project]=MAX('Table'[Project]))))
return Result

Estimate Hours = 
VAR LastVisibleDate = SELECTEDVALUE('Table'[End_Date])
Var FirstVisibleDate = MIN('Table'[Date])
VAR DayBetweenDates = DATEDIFF(FirstVisibleDate,LastVisibleDate,DAY)
return SELECTEDVALUE('Table'[Total_Estimate_Hours])/DayBetweenDates

If the method I provided above can't solve your problem, what's your expected result? Please provide a display photo of the desired result.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anthony_2022]

Hi @v-zhangti ,

Thank you for the answer.

Unfortunately, that doesn't answer the question.

The result I'm trying to achieve is to validate if we're going under the hourly budget or if it's costing us more time before we get to the end of the project.

To get closer to this result, I had to add a Start Date column to my data. I managed to get the desired modeling with this DAX formula:

Estimate Hours 4 = 
CALCULATE (
    DATEDIFF (
        SELECTEDVALUE ( 'Table'[Start Date] ),
        SELECTEDVALUE ( Dates[Date] ),
        DAY
    )
        * DIVIDE ( SELECTEDVALUE ( 'Table'[Total Estimate Hours] ), [Datediff] ),
    ALLEXCEPT ( Dates, Dates[Date] )
)

I still have the concern to have the estimated hours beyond the realized data. Here is a dummy example that I want to achieve:

Here what my model looks like: