Forum Discussion
How do I create a summarized table with the latest records based on a date?
Hello,
I'm trying to create a calculated table that is summarized with the latest values based on a date slicer.
Here is an example:
ColumnID UserName UserValue Date
| 1 | Charles | 1 | 1/23/2018 |
| 1 | Charles | 6 | 2/15/2018 |
| 1 | Charles | 4 | 4/2/2018 |
| 2 | Lisa | 4 | 3/1/2018 |
| 2 | Lisa | 8 | 3/27/2018 |
| 3 | Sam | 5 | 1/30/2018 |
| 4 | Ashley | 2 | 1/11/2018 |
| 4 | Ashley | 5 | 1/31/2018 |
| 4 | Ashley | 6 | 3/5/2018 |
| 4 | Ashley | 3 | 5/12/2018 |
If I use an As Of Date of 01/28/2018 Id like:
ColumnID UserName UserValue Date
| 1 | Charles | 1 | 1/23/2018 |
| 4 | Ashley | 2 | 1/11/2018 |
An As Of Date of 03/02/2018
ColumnID UserName UserValue Date
| 1 | Charles | 6 | 2/15/2018 |
| 2 | Lisa | 4 | 3/1/2018 |
| 3 | Sam | 5 | 1/30/2018 |
| 4 | Ashley | 5 | 1/31/2018 |
AsOF 05/01/2018
ColumnID UserName UserValue Date
| 1 | Charles | 4 | 4/2/2018 |
| 2 | Lisa | 8 | 3/27/2018 |
| 3 | Sam | 5 | 1/30/2018 |
| 4 | Ashley | 6 | 3/5/2018 |
Many thanks for your help!
Hi Anonymous
Create a date table without relationship with your table.
date = CALENDARAUTO()
add [Date] to the slicer,select "before" from the drop-down list.Create a measure in your tableMeasure = VAR maxselected = MAX ( 'date'[Date] ) VAR max_fit = CALCULATE ( MAX ( 'Table'[Date] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[ColumnID] ), [Date] < maxselected ) ) RETURN IF ( MAX ( 'Table'[Date] ) = max_fit, 1, 0 )add [Measure] to the visual level filter of the table as above
Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- amitchandak
Super User
If the user can use filter pane. Then you can Advance filter. There you have the option for <=. you can use page or visual level filter as per need
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. 
v-juanli-msftCommunity Support
Hi Anonymous
Create a date table without relationship with your table.
date = CALENDARAUTO()
add [Date] to the slicer,select "before" from the drop-down list.Create a measure in your tableMeasure = VAR maxselected = MAX ( 'date'[Date] ) VAR max_fit = CALCULATE ( MAX ( 'Table'[Date] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[ColumnID] ), [Date] < maxselected ) ) RETURN IF ( MAX ( 'Table'[Date] ) = max_fit, 1, 0 )add [Measure] to the visual level filter of the table as above
Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
