Forum Discussion

Tarek78210's avatar
Tarek78210
Frequent Visitor
2 years ago

First creation date per customer

Hi All,

 

I have one simple table with two columns:

 

- one column for the customergroup name

-one column showing when that customer group was created

 

A customer group can have multiple customers. That's the reason why we have multiple creation dates per customer group. I would like to create a dax that shows the first date of creation per customer group.

 

Example: CustomerGroup1 has 3 dates but the first one was created the 1/01/2021 and therefore, I want to see that date shown up in a new column.

 

Thanks very much for your help!

 

Date
DAX

4 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    2 years ago

    Hi,

    Please check the below picture and the attached pbix file.

    It is for creating a new column.

     

     

     

    INDEX function (DAX) - DAX | Microsoft Learn

     

    First Date CC =
SELECTCOLUMNS (
    INDEX (
        1,
        SUMMARIZE ( Data, Data[CustomerGroup], Data[CreationDate] ),
        ORDERBY ( Data[CreationDate], ASC ),
        ,
        PARTITIONBY ( Data[CustomerGroup] ),
        MATCHBY ( Data[CustomerGroup], Data[CreationDate] )
    ),
    Data[CreationDate]
)
    • Tarek78210's avatar
      Tarek78210
      Frequent Visitor
      2 years ago

      Thanks. For your information, I am on Power Pivot, not Power BI. For some reasons, all the formulas don't appear in the predictive text. For example: Selectedcolumns appear but not the Index function.

       

      Is your formula working with the Power Pivot environment?

  • Tarek78210's avatar
    Tarek78210
    Frequent Visitor
    2 years ago

    Thanks very much. Is it a column measure or DAX measure? Ideally, I would like a measure I can create as a new column

    • Jihwan_Kim's avatar
      Jihwan_Kim
      Super User
      2 years ago

      Hi,

      The suggestion that I provided was a calculated column, not a measure.

      For creating a measure, please check the below.

       

      First Date measure =
CALCULATE (
    MIN ( Data[CreationDate] ),
    INDEX (
        1,
        SUMMARIZE ( Data, Data[CustomerGroup], Data[CreationDate] ),
        ORDERBY ( Data[CreationDate], ASC )
    )
)