Forum Discussion
Get value based on date range filters
Hello all - is this possible?
I have a table called T1 with 3 columns - Id, Date, Qty
Another table called T2 with 3 columns - Id, Date, Type
The two tables are joined on Id. For one Id in T1, there can be multiple Types in T2.
How can I add a calculated column in T1 to get Type from T2 such that the column only picks that latest Type based on the date Something like - TOP 1, T2[Type] WHERE T2[DATE] <= T1[DATE].
Thanks in advance!
Hi ConstMoss,
In your scenario, you can create a calculated column in T1 like below:
Column = CALCULATE(MAX('T2'[Type]),FILTER(ALL('T2'),'T2'[ID]=EARLIER('T1'[ID]) &&'T2'[Date]<=RELATED(T1[Date])))
You can check attached .pbix file.
Best Regards,
Qiuyun Yu
2 Replies
Anyone?
Hi ConstMoss,
In your scenario, you can create a calculated column in T1 like below:
Column = CALCULATE(MAX('T2'[Type]),FILTER(ALL('T2'),'T2'[ID]=EARLIER('T1'[ID]) &&'T2'[Date]<=RELATED(T1[Date])))
You can check attached .pbix file.
Best Regards,
Qiuyun Yu