cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dawson16
Frequent Visitor

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 occurs at any point the month before the snapshot date and "No" if not. Snapshots are taken each month of all of the activities, so start and finish dates can vary between them. Here is an example table to show what I'm trying for. 

Dawson16_0-1663094392313.png

I figured that a formula like the following would work, but it's not quite right. Does anyone know of a solution to this?

In Date Range? =

var ActListDates=DATESBETWEEN('Date'[Date],Table[Start Date], Table[Finish Date])
var PriorMonthListDates = PREVIOUSMONTH(Table[Snapshot Date])
Return
if(COUNTROWS(INTERSECT(ActListDates, PriorMonthListDates))>=1, "Yes", "No")

3 ACCEPTED SOLUTIONS
Greg_Deckler
Super User
Super User

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

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.

latimeria_0-1663105298816.png

 

View solution in original post

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

 

View solution in original post

6 REPLIES 6
Greg_Deckler
Super User
Super User

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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.

PBI Support Picture.PNG

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.

latimeria_0-1663105298816.png

 

@latimeria You beat me to it!! 🙂


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

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

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors