Forum Discussion

kteigen's avatar
kteigen
New Member
3 years ago

Check if Date is between a Start & End date on Multiple Rows

Hi all,

 

I have a Fact table that has multiple rows each with their own Start & End date. I also have a Date table, and I want to add a column which checks to see if that Date is included in any of the start/end dates of the Fact table.

 

For instance, here's my Fact table:

 

And here's the Custom Column I'm trying to add to the Date table: 

 

 

So, if Ralph is working from 1/1/23 to 1/8/23, I want all those dates to show as "Yes" on the "Date"[IsAnybodyWorking?] column. I was hoping the formula I had would work but I'm getting a the Expression.Error shown in the 2nd screenshot.

 

What I hope to end up with is a final count of any days where at least 1 employee is working (based on the start & end dates on the Fact table). I've also tried solving for the problem in DAX with a new column in the data model, but didn't have any more luck with that approach. Any tips or tricks are greatly appreciated!

 

 

1 Reply

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion

    =if Table.Contains(Fact,_,(x,y)=>x[Start Date]<=y[Date] and x[End Date]>=y[Date]) then "Yes" else "No"