Forum Discussion

TimPowerBi2's avatar
TimPowerBi2
Frequent Visitor
3 years ago
Solved

Get the first date for a name over multiple rows

Hello Power BI Community!   I am trying to get the earliest and latest date for every name in a timeseries table. I want to do this through a calculated column so I can run further analysis on the ...
Need Help
  • MBernalBI's avatar
    MBernalBI
    3 years ago

    Hello!!

     

    I hope that I can help you. I have created a calculated table. 

    Desired result = 
SUMMARIZE(Tabla,  
Tabla[Name],
Tabla[Date],
"Earliest", CALCULATE(MIN(Tabla[Date]), ALLEXCEPT(Tabla,Tabla[Name])),
"Latest", CALCULATE(MAX(Tabla[Date]), ALLEXCEPT(Tabla,Tabla[Name]))
)

     

    This is the result

     

     

    I hope that I have helped you.

     

MBernalBI's avatar
MBernalBI
Frequent Visitor
3 years ago

Hi TimPowerBi2 

 

Which fields are you using in the relationship? 

TimPowerBi2's avatar
TimPowerBi2
Frequent Visitor
3 years ago

Hi MBernalBI 

 

I am using the Name fields.

  • MBernalBI's avatar
    MBernalBI
    Frequent Visitor
    3 years ago

    Hi TimPowerBi2 

     

    This occurs because the relationship between the Name fields is N:N. Table 1 shows N records for Michael and Table 2 also shows N records for Michael.

     

    I would use Table 2 for future analysis, since if the relationship were established as N:N, there could be duplicate problems. It is also a bad practice in data modeling.

     

    I hope tha it helps you and it's a good solution.

    • TimPowerBi2's avatar
      TimPowerBi2
      Frequent Visitor
      3 years ago

      Thanks a lot, that makes perfect sense and will work with it from there!