How do I create a summarized table with the latest records based on a date?

[Anonymous]

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!

[v-juanli-msft · Accepted solution]

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 table

Measure =
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
Maggie

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

[amitchandak]

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.