cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
msommerf Regular Visitor
Regular Visitor

Find value between 2 dates

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
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]
    )
)

View solution in original post

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

View solution in original post

msommerf Regular Visitor
Regular Visitor

Re: Find value between 2 dates

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

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

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?

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

January 2020 Community Highlights

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

Top Solution Authors