Forum Discussion
DAX - Getting a value based off the earliest and latest dates
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!
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.
3 Replies
- PattemManohar
Community Champion
BTLYBSCT Please provide the sample data and expected output which will be really helpful.
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.
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.
