cancel
Showing results for
Did you mean:
Frequent Visitor

Calculate value between two dates if date falls between two dates in another table

What I am trying to accomplish create a calculated column in my date table to return the value of a column from another table where I only have a start and end date. It should be pulling in the value of 'Event Header_Basic'[POS Event Code], if DimDate[Date] is between 'Event Header_Basic'[Date Start] and 'Event Header_Basic'[Date End].

What I've tried is:

```Column = CALCULATE(
VALUES('Event Header_Basic'[POS Event Code]),
FILTER(
&& 'Event Header_Basic'[Date End]>=(DimDate[Date])
)
)```

I recieved an error "A table of multiple values was supplied where a single value was expected.".

Does anyone have any other suggestions?

Thanks

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team

Re: Calculate value between two dates if date falls between two dates in another table

You may use DAX below.

```Column =
CONCATENATEX (
FILTER (
'Event Header_Basic'[Date Start] <= ( DimDate[Date] )
&& 'Event Header_Basic'[Date End] >= ( DimDate[Date] )
),
'Event Header_Basic'[POS Event Code],
","
)
```
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

Re: Calculate value between two dates if date falls between two dates in another table

You're the best, thanks!

3 REPLIES 3
Community Support Team

Re: Calculate value between two dates if date falls between two dates in another table

You may use DAX below.

```Column =
CONCATENATEX (
FILTER (
'Event Header_Basic'[Date Start] <= ( DimDate[Date] )
&& 'Event Header_Basic'[Date End] >= ( DimDate[Date] )
),
'Event Header_Basic'[POS Event Code],
","
)
```
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

Re: Calculate value between two dates if date falls between two dates in another table

You're the best, thanks!

Highlighted
Visitor

Re: Calculate value between two dates if date falls between two dates in another table

I have a similar case and this DAX "almost" works for me, the only challenge that I have is that I have multiple lines with multiple results, hence I end up getting something like "A,A,A,B,B,B,C,C". How can this formula be modified if I have 1 or 2 more criteria (columns) to match?

thanks in advance @v-chuncz-msft

Rgds,

Fer

Announcements

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)