Forum Discussion
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.
Please Help.
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
7 Replies
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/
There are ways in DAX to calculate values from previous rows....but they are complex and cumbersome.
