Retrieve amount based on date filter

[Learner79]

Invoice ID	planned amount	date
3	200	10/06/2022
3	300	23/06/2022
3	400	31/07/2022
4	700	19/06/2022
4	1000	23/06/2022
4	600	30/07/2022

Hello there,

I have a table akin to the above where an amount can change over time for an ID.  For ID 3, up until and including 22/06/2022, the amount was 200.  From 23/06/2022 to 30/07/2022, the amount was 300 and from 31/07/2022 and onwards it is 400.

This table is joined to a calendar table on date and there will be date filter.  

I want to be able to show the total amount at a point in time based on the date filter.

For e.g. if 01/08/2022 is selected on the filter, 1000 (400 + 600) will show as the total planned amount as the amounts included have dates that are closest to and on or before 01/08/2022 for both IDs (31/07 and 30/07).  For filter date of 20/06/2022, the total planned amount would be 900 (200 + 700) as ID 3 has date 10/06/2022 which is before the 20/06/2022 and ID 4 has a date of 19/06/2022 ie the closest date to the filter that is on or before the filter date.

The date filter will be used to show the planned amount total at the point in time of the date selected.  Is this possible?

thanks in advance.  Learner79

[Learner79]

Hello, thanks for your replies.  I've come across a snag.  Whilst the codes work for dates after the initial dates stated (June dates), they don't work for dates before the first date.  For e.g. if 03/05/2022, was selected, is there a way to return 200 and 700?  That is until the first date for each ID, the first planned amount is shown on prior dates then after that the latest date amount is shown? @Anonymous @tamerj1 

[tamerj1]

Hi @Learner79 

please try

Planned Value =
SUMX (
TOPN (
1,
CALCULATETABLE ( 'Table', 'Date'[Date] <= MAX ( 'Date'[Date] ) ),
'Table'[Date]
),
'Table'[Planned Amount]
)

[Learner79]

thanks Tamerj1, will give it a try.  Excuse my ignorance but will this take into account the date selected in the date filter? The date filter will have a list of every date in a calendar table e.g, 01/01/2022, 02/01/2022 etc that a user can enter a date into.

[Anonymous]

Hi @Learner79 ,

I suggest you to try code as below to create a measure.

Total amount at a point in time =
VAR _SELECTVALUE =
    SELECTEDVALUE ( 'Date'[Date] )
VAR _Filter =
    FILTER (
        ALL ( 'Table' ),
        'Table'[date]
            = CALCULATE (
                MAX ( 'Table'[date] ),
                FILTER (
                    ALLEXCEPT ( 'Table', 'Table'[Invoice ID] ),
                    'Table'[date] < _SELECTVALUE
                )
            )
    )
RETURN
    SUMX ( _Filter, [planned amount] )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.