Earliest Date from a column with Multiple Conditions

[MSW]

Hello,  I have been trying unsuccessfully to find a calculated column DAX solution for finding the earliest date per fiscal period. I have a set of dates in one column in a table as well as a column of duplicate customer IDs. Is it possible to find the earliest date for each fiscal period by customer ID? The formula would return the Earliest Date per Year Column.

CustomerID	date	Earliest Date per Year
123	1/1/2019	9/1/2018
456	2/1/2019	10/1/2018
789	3/1/2019	12/1/2018
123	9/1/2018	9/1/2018
456	10/1/2018	10/1/2018
789	12/1/2018	12/1/2018
123	3/1/2020	3/1/2020
456	4/1/2020	4/1/2020
789	5/1/2020	5/1/2020

[v-yanjiang-msft · Accepted solution]

Hi @MSW ,

According to your description, here's my solution.

Create two calculated columns.

FY =
IF (
    MONTH ( 'Table'[date] ) > 8,
    YEAR ( 'Table'[date] ) + 1,
    YEAR ( 'Table'[date] )
)

Earliest date per year =
MINX (
    FILTER (
        'Table',
        'Table'[CustomerID] = EARLIER ( 'Table'[CustomerID] )
            && 'Table'[FY] = EARLIER ( 'Table'[FY] )
    ),
    'Table'[date]
)

Get the expected result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please considerAccept it as the solution to help the other members find it more quickly.

[MSW]

@amitchandak That works for the calander year but not by fiscal year. Which in this case starts on Sept 1 through Aug 31. I have Fiscal year defined in a seperate dates table but not within the table trying to do the calculation on. How can I run the fiscal year parameter through this formula? 

[amitchandak]

@MSW , Try a new column like

new column =
var _year = year([date])
return
minx(filter(Table,[CustomerID] = earlier([CustomerID]) && year([date]) = _year), [Date])