How do I dynamically select the most recent record by part number?

[lizlindsay]

So I have a very large table of part transactions and I want to select the most recent transaction for each part. I know how to do this after figuring out the max date, but that's where I'm stuck. I am able to find the max date by partnum with this:

MaxDate = CALCULATE(LASTDATE('PartTran'[TranDate]), FILTER('PartTran', 'PartTran'[PartNum] = EARLIER('PartTran'[PartNum])))

But the problem is that it isn't dynamic, and doesn't change when I change the date on the slicer. This is because the columns do not get calculated dynamically. There's a few things that I've tried to get around this, but I keep getting an error message saying that a circular dependency was detected.

My data is something like this:

and I want to see something like this if today's date is selected

and something like this if the date '4/14/2016' on the slicer were selected

[ibarrau]

Hi, I agree with freder1ck about the reason you are doing this to help a way to get a better solution. I don't think this would be a good practice, but hope it helps.

New Column = 
VAR Actual_PartNum = PartTran[PartNum]
RETURN
MAXX(FILTER(PartTran; PartTran[PartNum]=Actual_PartNum ); PartTran[TranDate])

I'm confused with the date slicer you mention, can you be more specific?

Regards,

[freder1ck]

Hi Lizlindsay,

There are some tricky things about inventory. In this case, you will need to have a calendar table that's disconnected from your transaction table. The following measure should work.

Last Date:=
VAR Cutoff = LASTDATE('Calendar'[Date])
RETURN
     CALCULATE(MAX(Table2[TranDate]),
          FILTER(ALL(Table2[TranDate]),
          Table2[TranDate] <= Cutoff)

      )

Fred

As a followup, I highly recommend this article by Matt Allington. 
https://exceleratorbi.com.au/fill-table-with-last-survey-result/

[lizlindsay]

Hi Fred,

This did not quite work for me. For the most part, it worked correctly; it found the max date dynamically and it did in fact change when I picked a different date from the slicer. However, I did run into some of the same issues I was having before. I made sure I did everything exactly as you described in your response, but it's not providing the correct date in some cases. Do you have any idea as to why this might be happening or how to fix it?

While using the same formula as a column instead of a measure, I get the circular dependency error again. I also tried adding back in my filter of

FILTER('PartTran', 'PartTran'[PartNum] = EARLIER('PartTran'[PartNum]))

(which I am using so that it is grouped by part number), and as a column I wind up with the same results as my original non-dynamic max-date function I described.

I have no idea if either of those were even remotely in the right direction? I'm very new to Power BI (I've never made a report or anything of the sorts until the one that I'm attempting to get to work now) so I'm not sure where to even start for a lot of this stuff. I read the article you provided but I have not been able to get any further on the calculation.

I’m also open to any other ways to select the most recent row by part with a dynamic cutoff date if there’s other ways to accomplish this.

Thanks,

Liz

[freder1ck]

Liz, 

Measures and calculated columns have different advantages. Columns are great in lookup tables for categorizing data. Measures are ideal for dynamic calculations.

What are you trying to accomplish that would require a calculated column instead of a measure?

Thanks,

Fred