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

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.

 

TimPowerBi2's avatar
TimPowerBi2
Frequent Visitor
3 years ago

Thank you so much for the quick solution!

 

It works really well thank you. The only thing that is bugging me is that despite the name field having only unique values in the new custom table I can only create a many-to-many relationship between it and the original table.

 

Do you know why this is? 

  • 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.