Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Find last value within date range

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...
  • tamerj1's avatar
    tamerj1
    3 years ago

    Anonymous 
    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

     

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

Hi Anonymous 
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] )