Forum Discussion

PowerBIUser3492's avatar
PowerBIUser3492
New Member
3 years ago
Solved

Creating new Date Column or Measure based on certain values

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. 

Need Help

2 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    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]
    )
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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