Calculating days of overlap

[Anonymous]

Hello,

I've been trying to create DAX functions to calculate program overlapping days, but so far still unsuccessful.

What my goal is to calculate the number of days two or more program overlapped (program dates) and the dollar amount associate with the overlap.

I have a very large data table of following information:

Line #	Marketing Partner Code	Marketing Item Code	Marketing Program Code	Program Start Date	Programe End Date	Expense
1	Company A	PA	Hola 1	Jan 1 2019	Jan 31 2019	$500,000
2	Company A	PA	Hola 2	Jan 15 2019	Jan 18 2019	$120,000
3	Company A	PA	Hola 3	Jan 30 2019	Feb 20 2019	$95,000
4	Company B	PB	Hola 1	Jan 1 2019	Jan 31 2019	$500,000

I also created a calendar table so that the user can define the program validity dates like so:

In my business what can happen is for a given marketing partner and marketing item, there can be multiple marketing campaigns that might run simultaneous (either full or partial duration) and I want to know given those overlapping days, how much additional expense we spent/day.

I consider overlapping days with the following logic:

1. Concanate of Partner Code and Product code matches
2. The start and end date of the program code of #1 somehow overlaps (3 programs overlap from above data table)

With those overlapping days (line 1 to 3 overlaps: 4 days + 3 days), I want to get the simple average expense/day (e.g., Company A + PB + Program 1 Marketing Expense divided by the total validity days of Program 1 of the same combination). So

With this information, I am going to multiply the average expense/day, days overlapping AND product volume data (which is another table)

Would appreciate any help or tips. Thanks in advance!

[v-gizhi-msft · Accepted solution]

Hi,

Please try this measure:

Measure =
SUM ( 'Table'[Expense] )
    / COUNTROWS (
        FILTER (
            CALENDAR (
                MIN ( 'Table'[Program Start Date] ),
                MAX ( 'Table'[Programe End Date] )
            ),
            COUNTROWS (
                FILTER (
                    'Table',
                    'Table'[Program Start Date] <= [Date]
                        && 'Table'[Programe End Date] >= [Date]
                )
            ) >= 2
        )
    )

The result shows:

Best Regards,

Giotto Zhi

[Anonymous]

@Greg_DecklerThank you for the suggestion, but the code doesn't seem to work as intended, but this was a new DAX function that I think would be useful for my future use!

@v-gizhi-msftThank you so much for the suggestion. I understand the logic behind the division, but don't fully understand the first "filter" and the reason behind putting the 2nd "countrow" Could you help me understand please?

I have a very large data (50k+ lines) that has numerous Marketing Partner Code, Program Code, etc... Is there a way to adjust the code so that I can get the data based on the combination (concanate) of Partner Code and Marketing Item (e.g., if Partner C has a certain item with program and so does Partner G within certain length of overlapping program dates)

Also, my "date" slicer doesn't seem to interact with the data. How do I link my separate calendar table that I created?

[v-gizhi-msft]

Hi,

The first filter is to generate a duration date calendar, the second filter is to count the rows that the date from calendar table which is between start date and end date from another main table.

Best Regards,

Giotto Zhi

[Greg_Deckler]

I would think it would be something along the lines of:

Overlap Column = 
  VAR __Line = [Line]
RETURN
  COUNTROWS(
    INTERSECT(
      GENERATE([Program Start Date],[Program End Date]),
      GENERATE(
        MINX(FILTER(ALL('Table'),[Line] <> __Line),[Program Start Date]),
        MAXX(FILTER(ALL('Table'),[Line] <> __Line),[Program End Date])
      )
    )
  )