topic Re: First creation date per customer in DAX Commands and Tips

First creation date per customer

Tarek78210 — Tue, 27 Feb 2024 17:19:05 GMT

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!

Re: First creation date per customer

Jihwan_Kim — Tue, 27 Feb 2024 17:56:44 GMT

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] )

Re: First creation date per customer

Tarek78210 — Tue, 27 Feb 2024 18:17:43 GMT

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

Re: First creation date per customer

Jihwan_Kim — Tue, 27 Feb 2024 18:58:42 GMT

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 ) ) )

Re: First creation date per customer

Tarek78210 — Tue, 27 Feb 2024 19:08:23 GMT

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?