Forum Discussion
Change a column value based on a date slicer
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?
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]))
1 Reply
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]))