Find value between 2 dates

[msommerf]

Can somebody please help?

I have a table which has financial periods which are defined between two dates as shown:

PERIODS TABLE

START DATE     END DATE     PERIOD

30/06/2013      27/07/2013        1

28/07/2013      24/08/2013        2

25/08/2013      28/09/2013        3

I have a date table with my dates in it:

DATES

DATE

01/07/2013

02/07/2013

28/07/2013

27/08/2013

I am trying to add a calculated column to add my periods to my date table as follows:

DATE            PERIOD

01/07/2013     1

02/07/2013     1

28/07/2013     2

27/08/2019     3

I have tried the following DAX but get an error:

Column = calculate (

     values (    PERIOD TABLE [PERIOD]),

     filter (PERIOD TABLE,

     PERIOD[START DATE]<=MAX(DATE[DATE]) && PERIOD[END DATE]>=MIN(DATE[DATE]))))

The resultant column is blank.

Can anyone advise as to what I am doing wrong?

Any assistance appreciated.

[jdbuchanan71 · Accepted solution]

Hello @msommerf 

Give this a try for making the calculated column in your date table.

Period = 
CALCULATE (
    MAX ( 'PERIOD TABLE'[Period] ),
    FILTER (
        'PERIOD TABLE',
        'PERIOD TABLE'[Start Date] <= Dates[Date] &&
        'PERIOD TABLE'[End Date] >= Dates[Date]
    )
)

[msommerf]

Perfect! this works for me. Many thanks for your assistance.