topic Find last value within date range in DAX Commands and Tips

Find last value within date range

Anonymous — Tue, 29 Nov 2022 08:51:05 GMT

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?

Re: Find last value within date range

tamerj1 — Tue, 29 Nov 2022 09:07:54 GMT

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

Re: Find last value within date range

Anonymous — Tue, 29 Nov 2022 12:13:54 GMT

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.

Re: Find last value within date range

tamerj1 — Tue, 29 Nov 2022 13:27:21 GMT

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

Re: Find last value within date range

Anonymous — Tue, 29 Nov 2022 13:50:53 GMT

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.

Re: Find last value within date range

tamerj1 — Tue, 29 Nov 2022 14:36:59 GMT

Anonymous

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

Re: Find last value within date range

Anonymous — Wed, 30 Nov 2022 07:16:53 GMT

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.