Need Help Getting the Last Transaction Date per Customer

[VIZYUL]

Need some help solving this challenge.  Per the data table beow, I want to use a KPI to display the value for each client.  The challenge is a calculation that will always return the PREVIOUS DATES value for each Client.  For example, for Client 1, the KPI card displays 9.  I want to set the KPI's Target Goal to the PREVIOUS DATE from the MAX DATE for that Client.  For Client 1, the KPI's Target Goal would be the 15 from 9/10/2019.

I've tried many different formulas, but without success.  This may be due to a lack of understanding concerning Filter Context, CALCULATE, EARLIER, etc...

I have a DateDim as a date table connected to Date, not sure if that helps or not.

Please Help.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[kentyler]

The best time to calculate the previous date's value is BEFORE you load the data. Here is the best explanation I've found of why that is:

Alternatively, and arguably a better approach, you may use Power Query before your data hits the DAX engine. Power Query bypasses the issue with DAX (discussed in the next two paragraphs) in multiple ways - the most elegant (although advanced - there's simpler methods) would List.Accumulate().

However, and every other post missed this, the answer to "Is it possible to have a Calculated DAX column that uses the previous rows calculated value as an input? " is NO. This is a recursive question. DAX cannot do recursion.

The reason why is simple: when the engine is compiling your calculated columns the smallest atomic unit in the DAX engine is a column - not a cell, like Excel. DAX cannot calculate the first cell in a column, then the next, then the next etc because it has no concept of a cell. It calculates the entire column at once because there is nothing smaller. This is a very important concept to understand when first learning DAX.

https://www.reddit.com/r/PowerBI/comments/9m0b49/dax_refer_to_previous_row_of_same_column/

[VIZYUL]

@kentyler Thanks.  Totally understand this concept.  My question is, with a column in the data source that identifies the previous transaction date for a specific client, how then do I use that value to generate the KPI in the original thread?

[kentyler]

There are ways in DAX to calculate values from previous rows....but they are complex and cumbersome.

[VIZYUL]

@kentyler can you provide one of those methods, regardless how cumbersome it is?

[kentyler · Accepted solution]

here's a post with pretty good instructions

https://community.powerbi.com/t5/Desktop/How-to-get-previous-row-value-for-text-column/td-p/312560

[VIZYUL]

Worked perfectly @kentyler thanks a million!