Return me always the newest valid Date.

[Applicable88]

Hello,

I have a slowly changing dimension here. 

So I add a Valid from and Valid until column like this:

Date	Report	Description	Valid from	Valid until
09.06.2020	11111111	5555	01.01.2020	31.12.2999
09.06.2020	11111111	5555	08.06.2020	31.12.2999

Somehow the intervalmatch is not working well since valid until of the first one should be 07.06.2020.

I think I can use a workaround when I tell the table column to return me the one with the latest "valid from" date. which is  08.06.2020

I tried with:

=sum({<[Valid from]={"$(=Date(Max(Date)"}>}[Description]) defintely doesn't work out.

How to write a expression in set analysis to return only  that value?

Thanks in advance. 

[Kushal_Chawda]

Can you please provide sample data with expected output?

[Applicable88]

Hi,

I try to make an example :

Date	Report	Description
11.01.2020	11111111	5555
09.06.2020	11111111	5555

and another table:

Description	Material	Valid from	Valid until
5555	C3	01.01.2020	07.06.2999
5555	C4	08.06.2020	31.12.2999

When the report was made in 11.01.2020 , I want to return the Material at fault "C3"

when the report was made in 09.06.2020 I want to return the Material which is responsible for my report to be "C4"

Note at the "valid from" , "valid until" columns, they decide which interval was valid in the past or respective present.

Since table of material is very large, interval matching gives me too many values, almost like cross join. 

[Kushal_Chawda]

TableA:
Load Date, Report, Description
FROM TableA;

TableB:
Load [Valid From]+ietrno()-1 as Date,
     Description,
     Material
FROM TableB
[Valid From]+ietrno()-1 <= [Valid To]

Now you can simply select particular Date to get the description and material