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
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
4 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
4 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
    Icon for Solution Specialist rankSolution Specialist
    4 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.

     

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      4 years ago

      latimeria You beat me to it!! 🙂

      • 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! 🙂