Forum Discussion

MSW's avatar
MSW
Icon for Helper I rankHelper I
4 years ago
Solved

Earliest Date from a column with Multiple Conditions

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.

 

CustomerIDdateEarliest Date per Year
1231/1/20199/1/2018
4562/1/201910/1/2018
7893/1/201912/1/2018
1239/1/20189/1/2018
45610/1/201810/1/2018
78912/1/201812/1/2018
1233/1/20203/1/2020
4564/1/20204/1/2020
7895/1/2020 5/1/2020

 

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    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.

3 Replies

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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.

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    MSW , Try a new column like

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

  • MSW's avatar
    MSW
    Icon for Helper I rankHelper I
    4 years ago

    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?