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")

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
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")
Dawson16's avatar
Dawson16
Frequent Visitor
3 years ago

Greg_Deckler Thank you for your quick response! Unfortunately, this doesn't solve what I'm going for with this problem. I've attached some better sample data in a screenshot below as well as showed the formula I'm using. The rows I have highlighted yellow are giving improper results since their date ranges do coincide with the month preceding the respective snapshot dates. Please let me know if I can include any further images or data as needed to help clear up this issue.

  • latimeria's avatar
    latimeria
    Solution Specialist
    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
        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! 🙂