Forum Discussion
If Statement for Dates Between
I'm sure this is easy and I've tried more than a few solutions I've found on the boards already, but I can't seem to get it to work properly.
I'm tryign to create a conditional calculated column between two tables.
Table 1
Event - Begin Date - End Date
Table 2
Calendar Date
What I need is a measure in my calendar that identifies the day as an "Event day" or " Non Event".
I can't connect the two tables since this would introduce ambiguity, so ultimatly I need somthign like
Event_Day = IF( Table2[Cal_Dt] >= Table1[Bg_Dt] && Table2<= Table1[End_Dt], "Event", "No Event")
6 Replies
I think you want something like this as a calculated column on Table 2:
Event_Day = VAR CurrDate = Table2[Cal_Dt] VAR EventCount = COUNTROWS ( FILTER ( Table1, CurrDate >= Table1[Bg_Dt] && CurrDate <= Table1[End_Dt] ) ) RETURN IF ( EventCount > 0, "Event ", "No Event" )This gets me much closer, but instead of returning "Event" for the Days between [Bg_dt] and [End_Dt] it is returning "Event" between the Max and Min of Table1. Table1 has several rows of Events with various begining and end dates in an unrelated table. When I choose an event in a slicer I need to evaluate the calendar table with those begining and ending dates. However, the context of the slicer is not carrying over to the calcualted column.
It is not possible for a slicer to affect a calculated column, so you'd need to use a measure instead.
However, it's not clear to me how exactly you'd be using such a measure, so I'd need more context to understand know how to write it.
