cancel
Showing results for
Did you mean:
Frequent Visitor

## Need Help Getting the Last Transaction Date per Customer

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.

1 ACCEPTED SOLUTION
Solution Sage

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

Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!

Join the conversation at We Talk BI find out more about me at Slow BI

7 REPLIES 7
Super User III

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Solution Sage

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.

Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!

Join the conversation at We Talk BI find out more about me at Slow BI

Frequent Visitor

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

Solution Sage

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

Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!

Join the conversation at We Talk BI find out more about me at Slow BI

Frequent Visitor

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

Solution Sage

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

Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!

Join the conversation at We Talk BI find out more about me at Slow BI

Frequent Visitor

Worked perfectly @kentyler thanks a million!

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.