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

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. 

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

    Anonymous 

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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.