How to filter data from a data table using a date selector together with specific conditons

[SueM]

Hello

I have a data table consisting of deferral schedules from which I need to create a table showing transactions for each schedule by period, based on a date slicer.

If the schedule document date is less or equal to the selected date, the schedule and all transactions related to it must be included in the report. If the schedule is in the future (i.e. greater than the selected date, it must not be included.

A shedule has a transaction line number of 0, while the transactions of the schedule have transaction lines from1 onwards.

Below I have an example of the data file and two scenarios, each having a different selection date.

My problem is when I reference the selection table. I am unable to use a filter (to select only those current and past schedules) when calculating the Trans Amt for each specific period. My date slicer is set to 'Before', and the calculation used for each period is :

CALCULATE(Schedule[Total TranAmt],DATEADD(SelectedDate[Date],X,MONTH)) This works fine however, I still need to include the filter for current and previous schedules.

[SueM]

Thank you for your response.

Yes I have done that, but I still don't know how to filter those lines where the schedule document date is greater than the selected date.

I only want those where the schedule date is less or equal to the selected date, but calculate the tran amount on the Transaction date.

[amitchandak · Accepted solution]

@SueM , If you have date Table, You can create a measure like, In that case, Transaction date or schedule date should be joined with date table and date column from date table should be used

Then have measure like

CALCULATE(Schedule[Total TranAmt],DATEADD(Date[Date],X,MONTH))

CALCULATE(Schedule[Total TranAmt],DATEADD(Date[Date],1,MONTH))