DAX - Getting a value based off the earliest and latest dates

[BTLYBSCT]

Hi All,

I have a report that produces once a month and with every report I receive the same data set with a few new rows.

I have created a field for Month/Year so I can keep track of the increase and a value of a column "Contract Status" can change.

What I am trying to do is evaluate what the Contract Status was when it first came to the report and check what the contract status is after 6 months.

I have created two calculated columns

Earliest Date = CALCULATE(MIN('CCO Impairment_Asset_View'[Month/Year]), FILTER('CCO Impairment_Asset_View', 'CCO Impairment_Asset_View'[Contract No]=EARLIER('CCO Impairment_Asset_View'[Contract No])))

This one gives me the first date the contract number came into the report

Latest Date = DATEADD('CCO Impairment_Asset_View'[Earliest Date],6,MONTH)

Second one provides the date 6 months from the earliest date.

Now I am trying to find the value for Contract Status at the time it came in from the earliest and what the code is in the latest.

I have tried different methods and I seem to be stuck on this.

Any assistance would be greatly appreciated.

Thanks!

[PattemManohar]

@BTLYBSCT Please provide the sample data and expected output which will be really helpful.

[BTLYBSCT]

Apologies @PattemManohar, please see an example below.

Mnth	Contract Number	Contract Status
Feb-18	003-111111-000	7
Dec-17	003-111111-000	6

The above example shows that in Decemember when the contract came into the report it was flagged a contract status of 6. For the reports until Feb the data was showing the same until when the Feb one came through it was showing 7.

I would like to pick up the earliest contract status when a contract came into the account and then pick up any changes using my latest date forumula which would pick up the status of that code 6 months after the first date it came in.

Hope this is clear.

Thanks.

[BTLYBSCT · Accepted solution]

Hi All,

I have been able to solve this through sing the formula below.

ED Contract Status = 
CALCULATE(MAX('CCO Impairment_Asset_View'[Contract Status]),FILTER('CCO Impairment_Asset_View','CCO Impairment_Asset_View'[Contract No] = EARLIER('CCO Impairment_Asset_View'[Contract No]) && 'CCO Impairment_Asset_View'[Month/Year] = earlier('CCO Impairment_Asset_View'[Earliest Date])))

Hopefully this will be able to help someone in the future.