Forum Discussion

Dawson16's avatar
Dawson16
Frequent Visitor
3 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
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    Dawson16 You could do this:

    In Date Range? Column = 
  VAR __MonthBeforeSnapshot = EOMONTH([Snapshot Date]),-1)
  VAR __FinishDate = EOMONTH([Snapshot Date],0)
RETURN
  IF(__MonthBeforeSnapshot = __FinishDate,"Yes","No")
  • latimeria's avatar
    latimeria
    3 years ago

    Hi Dawson16 ,

    The Greg's answr is correct, just try it.

    In Date Range? =
VAR MonthStart =
    EOMONTH ( 'Intersect'[Start Date], 0 )
VAR MonthFinish =
    EOMONTH ( 'Intersect'[Finish Date], 0 )
VAR MonthBeforeSnapshot =
    EOMONTH ( 'Intersect'[Snapshot Date], -1 )
RETURN
    IF (
        MonthBeforeSnapshot = MonthStart
            || MonthBeforeSnapshot = MonthFinish,
        "Yes",
        "No"
    )

    The idea is to compare end of month for each dates.

     

  • Dawson16's avatar
    Dawson16
    3 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")

     

Dawson16's avatar
Dawson16
Frequent Visitor
3 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
3 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")