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.
Solved! Go to Solution.
You may download my PBI file from here.
Hope this helps.
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.
@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?
Check out new user group experience and if you are a leader please create your group!
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.
Click here to read more about the July 2021 Updates