Forum Discussion
Helper VCompare Data within related dates
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 |
İ 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>
5 Replies
- selpaqm
İ 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
Community Champion
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
Hi AllisonKennedy thanks to your reply, you can find the example as below, also is there any possiblity to do it with column?
- 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,
