New Column in Opportunity Table for Sales Opportunities Won in last Year

[PeterBlatchley]

I have a Sales Opportunities table where I am trying to create a new column that will show if the Account has a won opportunity in the past 365 days from the date the opportunity was created. I have a date table related to the create date column in the opportunity table. 

The logic for this new column is something like as follows:

is_new_Opp = if client has opportunity won in the last 365 days of the date the opportunity was created, then "New", else "Existing"

Model look like this:

This is sample data that includes the column I am trying to create "is_new_Opp".

[PeterBlatchley · Accepted solution]

@amitchandak ,

Thank you for your quick reply. I used your code and with one modification, it seems to be working. I had to add a filter to the VAR  "_last" to include that the opportunity needes to be won. It now reads:

Var _last = maxx(

    filter(prodOpportunities,

        [account_id] = earlier([account_id])  && [Opportunity_date_created] < earlier([Opportunity_date_created] ) && prodOpportunities[closed_won] = 1 )

        , [Opportunity_date_created])

return

Switch(True(),

isblank(_last), "New",

datediff(_last, [Opportunity_date_created], day) >365, "New" ,

"Existing"

)

[amitchandak]

@PeterBlatchley ,

New column=

Var _last = maxx(filter(Table, [Account] = earlier([Account])  && [Opportunity_date_created] < earlier([Opportunity_date_created]) ), [Opportunity_date_created])

return

Switch(True(),

isblank(_last), "New",

datediff(_last, [Opportunity_date_created], day) >365, "New" ,

"Existing"

)

The model needs to improve, You need two copies of Prod Account. One as Fact and one dimension

and Fact should not join with prodopportunity