cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## Find value between 2 dates

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV

## Re: Find value between 2 dates

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]
)
)```
2 REPLIES 2
Super User IV

## Re: Find value between 2 dates

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]
)
)```
Regular Visitor

## Re: Find value between 2 dates

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

Announcements

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors