Forum Discussion

Dawson16's avatar
Dawson16
Frequent Visitor
4 years ago
Solved

Intersect on Temporary Tables

Hello, I am trying to create a calculated column in my table that specifies whether the activity occurs during a certain month. For example, I want the calculated column to say "Yes" if the Activity ...
calculated column
DAX
Intersect
Dawson16's avatar
Dawson16
Frequent Visitor
4 years ago

Hi Greg_Deckler and latimeria, I must not have been following the DAX logic properly in Greg's original answer. I tried the longer formula posted above and it worked perfectly. Thank you both so much for helping me out with this! 🙂

Dawson16's avatar
Dawson16
Frequent Visitor
4 years ago

Greg_Deckler latimeria I just realized that the formulas above do not account for middle months if the activity spans more than two months. I believe I have found a solution to cover both instances, so I wanted to post it here for future guidance. 

In Date Range? = 
var ActivityListDates = DATESBETWEEN('Date'[Date],Table[Start Date],Table[Finish Date])
var PriorMonthListDates = DATESBETWEEN('Date'[Date],(EOMONTH(Table[Snapshot Date],-2)+1), EOMONTH(Table[Snapshot Date],-1))
	Return
IF(COUNTROWS(INTERSECT(PriorMonthListDates,ActivityListDates))>=1, "Yes", "No")