Share via

PowerPivot: Return Value if date in a range

Val Isted 21 Reputation points
2021-01-19T04:55:41.23+00:00

Hi,
I am using Excel PowerPivot.
I know this should be really simple to achieve but I'm struggling.
Thank you for you help in advance.

I have two tables.

Date Table:
StartDate,EndDate,Period

Time Records:
Date

All I want to do is add a column into the Time Records Table that returns the Date Table[Period] for the Time Records[Date]

Could you please assist me in the Dax required to achieve this.
Thanks

Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
4,066 questions
Not Monitored
Not Monitored
Tag not monitored by Microsoft.
40,268 questions
0 comments
Accepted answer
  1. Lz._ 9,006 Reputation points
    2021-01-19T07:29:10.847+00:00

    @Val Isted

    I might have misunderstood what you wanted to achieve in my previous reply. The following returns the DateTable[Period] where TimeRecords[Date] falls between DateTable[StartDate] and DateTable[EndDate]:

    =CALCULATE (
    MIN ( DateTable[Period] ),
    FILTER (
    DateTable,
    TimeRecords[Date] >= DateTable[StartDate]
    && TimeRecords[Date] <= DateTable[EndDate]
    )
    )

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.