Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
SueM
Frequent Visitor

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

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.

SueM_0-1708074850571.png

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.
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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))

View solution in original post

2 REPLIES 2
SueM
Frequent Visitor

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
Super User
Super User

@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))

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors