Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
Help when you know. Ask when you don't!
Hi,
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.
https://www.reddit.com/r/PowerBI/comments/9m0b49/dax_refer_to_previous_row_of_same_column/
Help when you know. Ask when you don't!
@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.
Help when you know. Ask when you don't!
@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
Help when you know. Ask when you don't!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |