Forum Discussion

meklund's avatar
meklund
Frequent Visitor
5 years ago
Solved

Calc Column marking earliest dates

Hello, 

Just started using DAX, powerpivot, and power BI and loving it. I'm trying to make a Calc Column for the earliest project date for each of our agents (sample data below). I imagine it looks something like SWITCH(TRUE,AND([Date of Project]=??,[agent code]=??),"FIRST PROJECT","") that goes into the flag column. 

but any help would be cool 🙂

AgentCodeProjectDateProjectNameFLAG?
1234566/4/2019Project 212 
1234563/1/2020Project 564 
1234569/23/2020Project 951 
2345679/2/2020Project 367 
45678912/7/2019Project 852 
4567893/9/2020Project 264 
Calculated columns
DAX
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    You should probably do this as a measure instead but it is common for people new to Power BI to overuse calculated columns.  Here is a column expression to get your result, but it would work as a measure too (in a table visual with the AgentCode column).

     

    First Project Date = CALCULATE(MIN(Table[ProjectDate]), ALLEXCEPT(Table, Table[AgentCode]))

     

    Regards,

    Pat

     

     

1 Reply

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    You should probably do this as a measure instead but it is common for people new to Power BI to overuse calculated columns.  Here is a column expression to get your result, but it would work as a measure too (in a table visual with the AgentCode column).

     

    First Project Date = CALCULATE(MIN(Table[ProjectDate]), ALLEXCEPT(Table, Table[AgentCode]))

     

    Regards,

    Pat