Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Create new table with MIN date

I want to create a new table from my table with customerId and startdates which returns one row per customer with the minimum dat per customer. I've tried to use SELECTCOLMUNS and FILTER and I get a table with single customerId's but the minimum date is not calculated per customer but from the total. How can I get the minimum date per customer?

Need Help
  • ErikHamoen's avatar
    ErikHamoen
    5 years ago

    Hey Ingrid,

     

    You can try the following:

     

    MinFvoorzieningTbvEerste=
    SUMMARIZE (
        'FvoorzieningTbvEerste',
        'FvoorzieningTbvEerste'[NrClient],
        "ingang", MIN ( 'FvoorzieningTbvEerste'[Ingang] )
    )

     

    Kind regards

4 Replies

  • ErikHamoen's avatar
    ErikHamoen
    Resolver I
    5 years ago

    Hey Ingrid,

     

    You can try the following:

     

    MinFvoorzieningTbvEerste=
    SUMMARIZE (
        'FvoorzieningTbvEerste',
        'FvoorzieningTbvEerste'[NrClient],
        "ingang", MIN ( 'FvoorzieningTbvEerste'[Ingang] )
    )

     

    Kind regards

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks ErikHamoen 🙏 this returns the values I needed.

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    I would make this table in the query editor with a Group By step.  Or you should use ADDCOLUMNS(SUMMARIZE(... instead of SELECTCOLUMNS which doesn't do any aggregation.

     

    Regards,

    Pat

     

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    Hi Anonymous 

    You could do something like:

    New Table =
ADDCOLUMNS (
    DISTINCT ( Table1[NrClient] ),
    "DatumIngang", CALCULATE ( MIN ( Table1[Ingang] ) )
)

     You might have to filter the first argument of the ADDCOLUMNS. If you explain what the filter exactly does in your code (I don't see it completely), we can get to a more accurate answer

     

    Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers