topic Re: Check if a date from a list is between two dates of another table in DAX Commands and Tips

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

Anonymous — Thu, 21 Apr 2022 16:37:54 GMT

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

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

tamerj1 — Thu, 21 Apr 2022 17:06:44 GMT

Hi Phil

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

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

Anonymous — Thu, 21 Apr 2022 18:14:39 GMT

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.

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

tamerj1 — Thu, 21 Apr 2022 19:10:03 GMT

Hi Anonymous

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

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

Anonymous — Fri, 22 Apr 2022 18:43:33 GMT

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

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

tamerj1 — Fri, 22 Apr 2022 18:46:30 GMT

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

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

Anonymous — Fri, 22 Apr 2022 19:05:30 GMT

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)

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

tamerj1 — Fri, 22 Apr 2022 19:08:37 GMT

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

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

Anonymous — Fri, 22 Apr 2022 19:22:06 GMT

That's what I'm doing though 😓

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

tamerj1 — Fri, 22 Apr 2022 19:41:46 GMT

Any chance you can share sample file?

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

tamerj1 — Fri, 22 Apr 2022 19:47:57 GMT

Anonymous

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

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

Anonymous — Fri, 22 Apr 2022 20:03:50 GMT

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=2uclvpcn1t23k9a6eopg8cajc

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

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

tamerj1 — Fri, 22 Apr 2022 20:08:10 GMT

Anonymous

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

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

Anonymous — Fri, 22 Apr 2022 20:14:14 GMT

It still returns the same error message unfortunately

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

tamerj1 — Sat, 23 Apr 2022 03:23:39 GMT

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

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

Anonymous — Sat, 23 Apr 2022 17:04:34 GMT

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