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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
SueM
Helper I
Helper I

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

View solution in original post

2 REPLIES 2
SueM
Helper I
Helper I

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.