Check if a date from a list is between two dates of another table

[pvdkamp]

Hi everyone,

I'm currently analyzing option data for my thesis and I'm trying to do the filtering in Power Pivot. One filter is to check if a dividend is paid over the duration of the option contract.

So I have two tables: One is for the Option data that contains the start date of the option contract and the expiry date as well as the underlying company name. The other table contains the company name and the ex-dividend dates.

I thought I could solve this via LOOKUPVALUE but since there's no unique key in the second table, it doesn't really work.

Can this be solved with DAX at all? 

Thank you so much in advance.

Yours,

Phil

[pvdkamp]

Thank you so so much @tamerj1, this worked!!! You made my day!

[tamerj1 · Accepted solution]

Good morning Phil

Here is the file with the solution https://www.dropbox.com/t/phfme1hP5qVxktZl

Flag :=
VAR StartDate = 'Stammdaten'[BASE OR ST DATE]
VAR EndDate = 'Stammdaten'[OPTION EXPIRY DATE]
VAR CurrentCompany = 'Stammdaten'[OPT.U/LYING NAME]
VAR DataDates =
    CALENDAR ( StartDate, EndDate )
VAR ContractsDates =
    CALCULATETABLE (
        VALUES ( 'Ex_Div_Tag'[Datum] ),
        FILTER ( 'Ex_Div_Tag', 'Ex_Div_Tag'[OPT.U/LYING NAME] = CurrentCompany )
    )
VAR Result =
    NOT ISEMPTY ( INTERSECT ( DataDates, ContractsDates ) )
RETURN
    Result

[tamerj1]

@pvdkamp 

Based on my understanding this column to be created in the Stammdaten table

New Column =
VAR StartDate = 'Stammdaten'[BASE OR ST DATE]
VAR EndDate = 'Stammdaten'[OPTION EXPIRY DATE]
VAR CurrentCompany = 'Stammdaten'[OPT.U/LYING NAME]
VAR DataDates =
    CALENDAR ( StartDate, EndDate )
VAR ContractsDates =
    FILTER (
        VALUES ( 'Ex_Div_Tag'[Datum] ),
        'Ex_Div_Tag'[OPT.U/LYING NAME] = CurrentCompany
    )
RETURN
    NOT ISEMPTY ( INTERSECT ( DataDates, ContractsDates ) )

[pvdkamp]

It still returns the same error message unfortunately

[tamerj1]

Hi Phil

would you please explain further the relationship between two table? How are the dates in both tables related

[pvdkamp]

Hi @tamerj1,

They are not per se related. The only relation they would have would be through the column "OPT.U/LYING NAME". 

I would need to check if, for example, for the first row in the first table with "OPT.U/LYING NAME" = Deutsche Bank, there is any ex dividend date for Deutsche Bank in the second table that lies between "Base or start date" 21.03.2017 and "Option Expiry Date" 19.05.2017. Since there is an ex dividend date 19.05.2017, I would assume the result to be true.

[tamerj1]

Hi @pvdkamp 

you can create a new calculated column in the first table (data table)

New Column =
VAR CurrentCompany = 'Option Data'[OPT.U/LYING NAME]
VAR DataDates =
    CALENDAR ( 'Option Data'[BASE OR ST DATE], 'Option Data'[OPTION EXPIRY DATE] )
VAR ContractsDates =
    FILTER (
        VALUES ( 'Option Contract'[Datum] ),
        'Option Contract'[OPT.U/LYING NAME] = CurrentCompany
    )
RETURN
    NOT ISEMPTY ( INTERSECT ( DataDates, ContractsDates ) )

[pvdkamp]

Hi @tamerj1

Thank you so much for your answer. However, I get the following error message when I try to create the column: "A single value for column 'OPT.U/LYING NAME' in table 'Option Contract' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

[tamerj1]

Are you sure you creating a New Column in the same table?

[pvdkamp]

Yep I am, unfortunately 😞

Btw, please don't be confused by the table names: 'Option Data' would be 'Stammdaten' in my file, and 'Option Contract' would be 'Ex_Div_Tag'

(Sorry had to edit the image since there was a mistake in the formula in the previous image)

[tamerj1]

The column should be created in the table that has start and end dates

[pvdkamp]

That's what I'm doing though 😓

[tamerj1]

@pvdkamp 

Please paste the exact code that you are using and indicate the name of the table which has the start snd end dates

[tamerj1]

Any chance you can share sample file?

[pvdkamp]

I uploaded a sample file to my Dropbox (I didn't know how else to do it on here) https://www.dropbox.com/scl/fi/lky9rbqrpec0mf9uqdc3b/Sample.xlsx?dl=0&rlkey=2uclvpcn1t23k9a6eopg8caj...

The Stammdaten table has the start and end dates and I used the code 

=
VAR CurrentCompany ='Stammdaten'[OPT.U/LYING NAME]
VAR DataDates =CALENDAR ('Stammdaten'[BASE OR ST DATE],'Stammdaten'[OPTION EXPIRY DATE])
VAR ExDivDates =FILTER (VALUES ('Ex_Div_Tag'[Datum]),'Ex_Div_Tag'[OPT.U/LYING NAME]=CurrentCompany)
RETURN
NOT ISEMPTY (INTERSECT (DataDates,ExDivDates ))