Creating new Date Column or Measure based on certain values

[PowerBIUser3492]

Hey Everyone, 

I've been racking my brain trying to create a couple new date columns in Power BI based on certain critieria and values. Below you will see my current table:

This is my desired outcome. You will see I am adding two columns - "Original Purchase Date" and "Most Recent Purchase Date."

I would post my excel file, but this window will not let me attach files... I guess I don't have privileges? And I tried post a table, but I am getting an HTML error. Thank you for your help. 

[Anonymous · Accepted solution]

Hi @PowerBIUser3492 ,

Please try:

Orginal Purchase Date = 
CALCULATE (
    MIN ( 'Table'[Date] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Type] = "Purchase"
            && 'Table'[ID] = EARLIER ( 'Table'[ID] )
    )
)

Most Recent Purchase Date = 
CALCULATE (
    MAX ( 'Table'[Date] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Type] = "Purchase"
            && 'Table'[Date] <= EARLIER ( 'Table'[Date] )
    )
)

//I would post my excel file, but this window will not let me attach files...

You can link to a cloud file. Make sure the file doesn't contain any sensitive data before you make it public.

How to provide sample data in the Power BI Forum

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[FreemanZ]

hi @PowerBIUser3492 

try to add two calculated columns like:

OriginalDate =

VAR _table = 

FILTER(

    TableName, 

    TableName[ID]=EARLIER(TableName[ID])

)

RETURN

MINX(_table, [Date])

RecentDate =

VAR _table = 

FILTER(

    TableName, 

    TableName[ID]=EARLIER(TableName[ID])

)

RETURN

MAXX(

    FILTER(_table, TableName[Date]<=EARLIER(TableName[Date])

    ),

    [Date]

)