Compare Data within related dates

[selpaqm]

Hi,

can we have a chance to compare values within related dates? 

Table 1 and Table has relation with names table also which is unique.

how can we find out results for dates? for example Result for AAAA on a specific date. (Example is in table 2)

(TABLE1)

Name	Currency	Valid from	Valid to
AAAA	1.08	05/07/2017	31/12/2017
AAAA	1.15	01/01/2018	01/01/2019
AAAA	1.20	02/01/2019	01/01/2020
BBBB	1.12	05/12/2016	31/08/2017
BBBB	1.17	01/09/2017	01/01/2019
BBBB	1.28	02/01/2019	01/01/2020

(TABLE 2)

Name	Date	Result
AAAA	01/05/2019	1.20
BBBB	01/08/2019	1.28

[selpaqm · Accepted solution]

İ have solved my own problem with below calculated column formula.

Column2 = CALCULATE(VALUES(Table1[Currency]),FILTER (
Table1,
Table1[Valid From] <= 'Table 2'[Date] &&
Table1[Valid To] > 'Table 2'[Date] && 'Table 2'[Name]=Table1[Name]
))<div> </div>

[AllisonKennedy]

@selpaqm  Well done! That's basically what I was getting at, just got the column names confused. My example given just adds an extra error check to ensure that only 1 value for currency remains after all the filtering you have done, so will 100% ensure that your column evaluates properly, even if data updates cause that measure to no longer result in 1 value for currency, it will return blank rather than error as in your solution. 

[AllisonKennedy]

You will need to check IF the selected date is <= Valid to date and > Valid from date. If you provide a bit more info on table names of data model or sample file we can be more helpful, but that is the general idea. 

@selpaqm  I have updated with a specific example for you to try: 

Assuming that Table 2 has both the Name and Date as columns, you could simply add a Column to Table 2: 

Column =
CALCULATE (
IF (
HASONEVALUE ( Table1[Currency] ),
VALUES ( Table1[Currency] )
),
FILTER (
Table1,
AND (
Table1[Valid From] <= Table2[Date],
Table1[Valid To] > Table2[Date]
)
)
)

[selpaqm]

@AllisonKennedy I have checked the example that you are assuming however table1 has no column named as date. so can you please check my example on below link. regards,

Example 

[selpaqm]

Hi @AllisonKennedy thanks to your reply, you can find the example as below, also is there any possiblity to do it with column? 

Sample.pbix