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.
Community ChampionBTLYBSCT 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.