Forum Discussion
DAX - Getting a value based off the earliest and latest dates
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.
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.