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

[nag_via]

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 ?

[olgad · Accepted solution]

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

[olgad]

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. 

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

[nag_via]

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.