Issue dealing with multiple date

[yayamiko]

Hi,

Let's say i have a table that describe a list of items and their day of order and their shipping day.

So i have two differents date.

The thing is i need a report that say the number of items with a shipping date greater that a certain date 'd' and a Order date lower that the same certain date 'd'.

So the filter will be on this certain date d

But i have now idea how to do it.

I know i have to create a time table and connect it to the two Order and Shipping dates but the relationship doesn't work. And i know i have to create a measure with USERELATIONSHIP but don't know what i am supposed to do.

If someone can help me that would be great.

It seems like this issue is famous in the PowerBi world.

Here my data model

( do i have to connect the time table to the star date/end date of my fact table ?)

and a dax measure i tried but ended with "empty" 

Thanks.

[johnt75 · Accepted solution]

You don't need a relationship for this scenario, you can use something like

Num items =
VAR ReferenceDate =
    MAX ( 'Date'[Date] )
VAR Result =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        'Table'[Order date] < ReferenceDate
            && 'Table'[Shipping date] > ReferenceDate
    )
RETURN
    Result

[yayamiko]

Hi @johnt75 .

Thank you so much. This is exactly what i wanted.

i came up the same dax measure but my variable ReferenceDate was equal to 

selectedvalue(date[date])

and it didn't do anything.

Thanks