Forum Discussion

ShocKrates's avatar
ShocKrates
New Member
1 year ago
Solved

Calculated Column that evaluates multiple rows

I have a table like the one below:

Project IDSpend DateFirst Spend Month
11/1/20241/1/2024
12/2/20241/1/2024
25/1/20245/1/2024
28/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?

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

3 Replies

  • gmsamborn's avatar
    gmsamborn
    Super User
    1 year ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.