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 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?

  • 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

     

6 Replies

  • tamerj1's avatar
    tamerj1
    Community 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] )
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      tamerj1
      Community Champion
      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

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        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.