Forum Discussion
Turning a Measure into a Column when calculating max dates
Hi all,
I have a measure that works to calcualte the maximum date from a range of dates (amended, document uploaded, contacts modified) which I am using to identify the last time a record has been interacted with. By using measures I have been able to compare these using MAX and return a single value against each ID which is great, but Measures do not appear to work as slicers or when trying to report back on values on a Card visual.
As an example, I have:
| ID | amended | document uploaded | contacts modified |
| 1 | 10/10/2023 | 01/02/2023 | 08/08/2021 |
| 2 | 05/06/2023 | 10/10/2023 | 08/09/2022 |
| 5 | 01/08/2020 | 09/12/2021 | 05/10/2023 |
| 1 | 01/01/2024 | ||
| 5 | 02/02/2024 |
So, for each ID the latest date would be:
1 - 01/01/2024
2 - 10/10/2023
5 - 02/02/2024
How would I approach this as a calculated column?
Anonymous Measure is one that will respond to slicer value
measure should be like - Maxx({Max(Table[amended]),Max(Table[document uploaded] ), Max(Table[contacts modified])}, [Value])
Column will be like
calculate(Maxx({(Table[amended]),(Table[document uploaded] ), (Table[contacts modified])}, [Value]), filter(Table, Table[ID] = Earlier(Table[ID])))
2 Replies
- amitchandak
Super User
Anonymous Measure is one that will respond to slicer value
measure should be like - Maxx({Max(Table[amended]),Max(Table[document uploaded] ), Max(Table[contacts modified])}, [Value])
Column will be like
calculate(Maxx({(Table[amended]),(Table[document uploaded] ), (Table[contacts modified])}, [Value]), filter(Table, Table[ID] = Earlier(Table[ID])))
Thanks, for my Measures (as document uploaded is a separate table), I had used the MAX function to the the MAX of each and then compared those to get the final number at the end
Max_date Amended= CALCULATE(MAX(table[amended]), ALLEXCEPT(table, table[ID]))Max_date Modified = CALCULATE(MAX(table[modified]), ALLEXCEPT(table, table[ID]))And thenMax_date = MAX('Table'[Max_date Amended], 'Table'[Max_date Modified])etcThis way I get a single date value for each ID via the measure, which is what I need - something that looks across all the dates associated with the ID and returns a single latest date. My issue with the Measure is that it can produce a table of data, but it's not as useful when trying to use for Totals on Card visuals for example.I have just realised that I could use the 'Latest' filter on the data to reduce this to the most recent date against each ID, so it may well be that you've already solved my query - I will explore further.Thanks!