Find last value within date range

[FrancisGhao]

Hi all,

I have trouble getting the appropiate value. I have 3 tables: date (), product and transactions. There is 1 to many link from date to product and transaction. 

Measure used: 

Prod Desc disregarding filter =

    VAR Currentprod = SELECTEDVALUE(TransactionTable[ProductID])

    RETURN

    MAXX(

        FILTER(ALL('ProductTable'), ProductTable[ProductID] =  Currentprod),

            ProductTable[Product Description])

Current Dax gives me the latest description (March) disregarding the filter selection Jan/Feb. I would like to see Jan description of product 1 when I select Jan and Feb Description of product 1 when I select Feb. 
If I select April, it would give me March (last available data)

Is there a dax formula that I should be using?

[FrancisGhao]

Thank you for the quick response. 
I copied the DAX, but it is not giving the desired result.
It is giving me the description of product 1 in Feb, but I lost the last available information of product 2 and 3. 

The desired result should be:

By the way, here is the link to the File. 

[tamerj1 · Accepted solution]

@FrancisGhao 
If you haven't shared the file, I wouldn't in a milion years figure out that in fact the Dates in the Product Table are in 2022 while the Dates in the Transaction Table are in 2018. The Dates in the Product Table do not even exist in the Date table! I was about to go crazy woundering why The Product Table do not exist inside the matrix even if the three months are were selected. 

Please refer to your sample file amended with the solution

Prod Descr = 
VAR Currentprod =
    SELECTEDVALUE ('TransactionTable'[ProductID])
VAR T1 =
    CALCULATETABLE ( 
        ProductTable,
        ALL ( DateTable ),
        ProductTable[ProductID] = Currentprod,
        MONTH ( ProductTable[Date] ) <= MONTH ( MAX ( DateTable[Date] ) )
    )
VAR T2 =
    TOPN ( 1, T1, ProductTable[Date] )
VAR Result =
    MAXX ( T2, ProductTable[Product Description] )
RETURN
    Result

[FrancisGhao]

Sorry about the dates. I discovered the date after the initial upload and have reuploaded the file. But I was too late. 
Thank you for the solution. I have some excel background, but the transition to dax is not easy. Online tutorials and the https://learn.microsoft.com/ are a bit weak in the department of combining Dax funtions. 

[tamerj1]

@FrancisGhao 

If the dates are fixed then the first solution should work. 

[FrancisGhao]

Yes it is working now. I also marked "accept as solution" yesterday. 
FIrst post was a success :). Start of a new journey to get to know PowerBI. 

[tamerj1]

Hi @FrancisGhao 
Please try

Prod Desc =
VAR Currentprod =
    SELECTEDVALUE ( TransactionTable[ProductID] )
VAR T1 =
    FILTER ( ProductTable, ProductTable[ProductID] = Currentprod )
VAR T2 =
    TOPN ( 1, T1, ProductTable[Date] )
RETURN
    MAXX ( T2, ProductTable[Product Description] )