cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
VIZYUL
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.

 

Please Help.

 

image002.jpg

1 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





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


View solution in original post

7 REPLIES 7
Ashish_Mathur
Super User III
Super User III

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
kentyler
Solution Sage
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.

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





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


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

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


VIZYUL
Frequent Visitor

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

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


View solution in original post

VIZYUL
Frequent Visitor

Worked perfectly @kentyler thanks a million!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

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.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors