Change a column value based on a date slicer

[Anonymous]

I have a Sales table

order_no	order_date	fee	projected_payment_date	payment_date	paid
1	2020-01-13	1000	2020-04-15		FALSE
2	2020-02-22	2000	2020-06-13	2020-06-15	TRUE
3	2020-02-27	3000	2020-07-01	2020-07-12	TRUE
4	2020-03-14	4000	2020--07-19		FALSE

And I calculate the value of the outstanding payments with the following measure:

Projected Revenue: No Payments Received = 

CALCULATE(CALCULATE(SUM(Sales[fee]),FILTER(Sales,Sales[paid]=FALSE))),USERELATIONSHIP(Sales[projected_payment_date],Dates[date]))

which would give the sum of the fee for order 1 and 4.

Now I want to be able to go back in time and see a snapshot of the payments that have yet to be received at that time.

So if I select 1 July 2020 in a Date Slicer, it should sum up the fees for 1,3 and 4, effectively treating the paid column for order 3 as FALSE instead of TRUE. So basically any payment_dates after the selected date, should change the paid column to false.

Any ideas on how I can achieve this?

[Anonymous · Accepted solution]

This is what I ended up doing

Snapshot = 
VAR max_date = CALCULATE(MAX(Sales[order_date]),ALLSELECTED())
RETURN
CALCULATE(
(SUMX(CALCULATETABLE(Sales,Sales[order_date]<=max_date),[fee])-
SUMX(CALCULATETABLE(Sales,Sales[order_date]<=max_date,Sales[payment_date]<=max_date,Sales[paid]=TRUE),[fee])),
USERELATIONSHIP(Sales[projected_payment_date],Dates[date]))