Help with measure to return date + 5 years

[Anonymous]

Hello

I'm connected to a tabular model and cannot create columns or change relationships. 
I'm connected via direct query.

I need to create a measure that will return something that indicates that we are approaching a 5 year anniversary date.

I only need to know the 5 year date on rows/records where there is a 5 year contract.

I cant seem to create anything that helps.

I can't supple the pbix file due to confidentility. 

[v-danhe-msft]

Hi @Anonymous,

Could you please post some picture of file about your data structure to have a test and post your desired result if possible?

Regards,

Daniel He

[Anonymous]

@v-danhe-msft sorry, the desired result in each column would be :

Min 5 year = 04/05/2023

and max 5 year = 30/06/2012

That would populate down the column for each row Person One has a value in the data for.

[v-danhe-msft]

Hi @Anonymous,

Form your description, could you please offer more logic information about how to calculate the Min and Max data:

Min 5 year = 04/05/2023

and max 5 year = 30/06/2012

Regards,

Daniel He

[Anonymous]

sorry @v-danhe-msft i'll step this out some more. I'm really thankful for your help.

I'll attach a new image.

I would like to create a measure to calculate and return a date.

Based on the table below, this is the purchase history for Person 1.

You will see they have two 5 year contract types. They are the records i need to calculate on.

I need to return the minimum 5 year end date in one measure and the maximum 5 year end date based on the results of the purchase start date. 
In the image, the light blue purchase start date matches up with the minimum purchase start date in column I, as this was the minimum purchase start date for a 5 year contract.

The dark blue is the same idea, but for the later 5 year contract and returns the maximum possible date for all 5 year contracts person 1 has taken out. 

In excel i've just added 1825 days to the purchase start date.

In power BI, i'm using direct query and cannot create new columns in my dataset. 

So the calculation and + 5 year logic to the purchase date needs to be in cotext to the Person. There is a Person ID which i'd imagine i'd use in the measure.

[Anonymous]

Hello. I've snipped images from an excel workbook that mocks up the kind of structure and the table showing the measure(s) i'm after help with. 

As you'll see from the images, i'm looking to calculate the min and max possible 5 year anniversary dates only where a person has a contract type of = 5 Year. 

I've tried a bunch of options but i can't get anything to return. I'm getting the context wrong and i'm sure i've over complicated it.

I am under the assumption that my only option is a measure, as i'm connected to a tabular analysis service database and don't have the ability to create columns or similar.  

I also cannot use quick measures due to the date field not being identified properly by the source owner.