Calculated Column that evaluates multiple rows

[ShocKrates]

I have a table like the one below:

Project ID	Spend Date	First Spend Month
1	1/1/2024	1/1/2024
1	2/2/2024	1/1/2024
2	5/1/2024	5/1/2024
2	8/1/2024	5/1/2024

Columns 1 and 2 are part of the dataset.  Column 3 is a calculated column.

Purpose of column 3 is to look at all the possible dates related to a single project ID and return the earliest date--the "First Spend Date"

I got this working with measures pretty cleanly, but I need to do some slicers later on in the process based off this data, and ran into these "no measures for slicers" problem.

So I'm trying to get this working with calculated columns.  Any idea what the DAX for column 3 would be to get it to calculate the First Spend Date?

[ShocKrates]

Thank you all for your help!  It worked 🙂

[Anonymous · Accepted solution]

Hi, @ShocKrates 

Thanks for gmsamborn's concern about this issue.

I am glad to help you.

You can refer to my DAX to New Column:

First Spend Month = 
CALCULATE(
    MIN('Table1'[Spend Date]),
    ALLEXCEPT('Table1', 'Table1'[Project ID])
)

Or

First Spend Month = 
VAR CurrentProjectID = 'Table1'[Project ID]
RETURN
CALCULATE(
    MIN('Table1'[Spend Date]),
    FILTER(
        'Table1',
        'Table1'[Project ID] = CurrentProjectID
    )
)

This is test result:

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[gmsamborn]

Hi @ShocKrates 

Would a column like this help?

_Result = 
VAR _Proj = [Project ID]
RETURN
    CALCULATE(
        MIN( 'Table'[Spend Date] ),
        ALL( 'Table'[Spend Date] ),
        'Table'[Project ID] = _Proj
    )

Let me know if you have any questions.