How to create DAX measure that returns initial result instead of aggregating

[zoop]

Hi all,

I have some project related data which contains the project name, tasks within the project, along with the time worked per person per day, and the initial estimate of effort for the overall task.

What I would like to do is create a measure for the 'Initial Estimate' field, which when used in a table will provide the original estimate, instead of aggregating the value. One caveat is that I would like the initial estimate to be summed at a project level. Please see some sample data below along with expected results.

Project	Task	Name	Date	Time Worked	Initial Estimate
A	Task 1	George	20/07/2022	1	4
A	Task 2	George	21/07/2022	1	3
A	Task 1	Fred	20/07/2022	2	4
B	Task 3	George	20/07/2022	2	7
B	Task 3	Fred	21/07/2022	2	7
B	Task 3	George	22/07/2022	2	7
B	Task 4	Fred	23/07/2022	2	5
B	Task 4	George	24/07/2022	3	5

Expected output with no date filters:

Project	Time Worked	Initial Estimate
A	4	7
Task 1	3	4
Task 2	1	3
B	11	12
Task 3	6	7
Task 4	5	5
Grand Total	15	19

Expected output with date filter set to only include 20/07 and 21/07:

Project	Time Worked	Initial Estimate
A	4	7
Task 1	3	4
Task 2	1	3
B	4	14
Task 3	4	14
Grand Total	15	19

What I have tried:

• Creating an average of the 'Initial Estimate' works in the table at a task level, however at a project level it just gives the average instead of the sum of the values.
• Creating a proportional 'Initial Estimate' where it takes the whole value and divides it by the number of tasks works well when aggregating, however doesn't work correctly when a date filter is set as it can exclude a portion of the estimate, resulting in a lesser value.

Any help would be much appreciated! 

[tamerj1 · Accepted solution]

Hi @zoop 
You can use the following formula

Initial Estimate Time = 
SUMX (
    SUMMARIZE ( 
        Sheet1, 
        Sheet1[Project], 
        Sheet1[Task] 
    ),
    CALCULATE (
        VAR InitialDate = 
            MIN ( Sheet1[Date] )
        VAR InitialEstimate = 
            CALCULATE ( SELECTEDVALUE ( Sheet1[Initial Estimate] ), Sheet1[Date] = InitialDate )
        RETURN
            InitialEstimate
    )
)

[zoop]

Thanks @tamerj1, this worked perfectly. Really appreciate the help!!