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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
nag_via
Frequent Visitor

1 Date table, how to filter dates on multiple relationship with condition at same time ?

Hello,

 

I did not how to name my post correctly but I have a fact table with multiple dates columns and 1 Date table. I compute my measures using userelationship. I have a case where I want to filter with multiple condition at the same time like: I have order date column and delivery date column and I want to be able to compute the total amount that was ordered before a certain date and delivered after another date. Exemple: Sales amount ordered before January 1st and deliverd after january 8.

If I had 2 date dimension it will be easy I will simply filter the order date dimension on dates before January 1st and my delivery date on dates after january 8.

But with only 1 date dimension and 2 relationship on the sales table , is it possible ?

 

1 ACCEPTED SOLUTION
olgad
Super User
Super User

For the slicers you would need then the second data table which will not have any relationship to any table acting as parameter.

You put Date slicer 1 from Date table 1 for order date and Date slicer 2  from Date Table 2 for delivery

 

in the formula then you use Calculate(Sum(Sales), Filter (Sales Table, Delivery Date >=Selectedvalue(DateTable 2 Date) && Order Date <=SelectedValue(Date Table 1 Date))


DID I ANSWER YOUR QUESTION? PLEASE MARK MY POST AS A SOLUTION! APPRECIATE YOUR KUDO/LIKE!
PROUD TO BE A SUPER USER!
Best Stories, Interesting Cases: PowerBI Storytime Newsletter
Linkedin Profile: Linkedin
YouTube Channel: PowerBI Storytime

View solution in original post

3 REPLIES 3
olgad
Super User
Super User

For the slicers you would need then the second data table which will not have any relationship to any table acting as parameter.

You put Date slicer 1 from Date table 1 for order date and Date slicer 2  from Date Table 2 for delivery

 

in the formula then you use Calculate(Sum(Sales), Filter (Sales Table, Delivery Date >=Selectedvalue(DateTable 2 Date) && Order Date <=SelectedValue(Date Table 1 Date))


DID I ANSWER YOUR QUESTION? PLEASE MARK MY POST AS A SOLUTION! APPRECIATE YOUR KUDO/LIKE!
PROUD TO BE A SUPER USER!
Best Stories, Interesting Cases: PowerBI Storytime Newsletter
Linkedin Profile: Linkedin
YouTube Channel: PowerBI Storytime
olgad
Super User
Super User

Create two relationships on is date to order date the second date to delivery date. The first one will be active, the second inactive one. 

olgad_0-1673730874593.png

 


The measure for the delivery amount you will calculate using USERELATIONSHIP
Order Amount=     Sum(Sales[Revenue])

Delivery Amount = Calculate(Sum(Sales[Revenue]), USERELATIONSHIP('Date'[Date],Sales[Delivery Date]))

DID I ANSWER YOUR QUESTION? PLEASE MARK MY POST AS A SOLUTION! APPRECIATE YOUR KUDO/LIKE!
PROUD TO BE A SUPER USER!
Best Stories, Interesting Cases: PowerBI Storytime Newsletter
Linkedin Profile: Linkedin
YouTube Channel: PowerBI Storytime
nag_via
Frequent Visitor

Hi,

 

It is what I am currently using in my models.

 

But my requirement is to be able to compute "the intersection of sales" using Delivery Date and Order Date at the same time but using 1 Date Table. Per exemple:  Sales where DeliveryDate >= 2023/01/08 and OrderDate <= 2023/01/01.  Maybe to have 2 slicer on the page to select the 2 dates range. 

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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