Forum Discussion
Find previous date for given value
Hi,
I'm trying to create a calculated column that will show the latest previous date for an associated value. In the example below, I have the "Date" and "Value" columns, and I'm trying to generate the "Desired Result" column. I'm tracking survey responses, to know when the last time is that a given person responded to a survey before the current response.
Example data:
| Date | Value | Desired Result |
| 1/1/2019 | Doug | |
| 1/2/2019 | Doug | 1/1/2019 |
| 1/8/2019 | Doug | 1/2/2019 |
| 1/4/2019 | Ben | |
| 1/7/2019 | Ben | 1/4/2019 |
| 1/10/2019 | Ben | 1/7/2019 |
Thanks in advance!
Hey tlong
use this DAX statement to create a calculated column:
Column = var _group = 'Table'[Value] var _date = 'Table'[Date] return CALCULATE( MAX('Table'[Date]) , FILTER( ALL('Table') , 'Table'[Date] < _date && 'Table'[Value] = _group ) )The result will look like this:
Please be aware that depending of number of rows in your dataset can become incredibly slow, this is simply because the formula and storage engine do not support windowing operations like this.Hopefully this is what you are looking for.
Regards,
Tom
7 Replies
Hey tlong
use this DAX statement to create a calculated column:
Column = var _group = 'Table'[Value] var _date = 'Table'[Date] return CALCULATE( MAX('Table'[Date]) , FILTER( ALL('Table') , 'Table'[Date] < _date && 'Table'[Value] = _group ) )The result will look like this:
Please be aware that depending of number of rows in your dataset can become incredibly slow, this is simply because the formula and storage engine do not support windowing operations like this.Hopefully this is what you are looking for.
Regards,
Tom
Hi TomMartens ,
I used your solution in my report and it works fine. But there is a problem with the refresh. On desktop report refreshes nicely but when I published it to service and tried to refresh I got the error:Data source error: Memory error: Memory Allocation failure . Try simplifying or reducing the number of queries.
Cluster URI: WABI-NORTH-EUROPE-I-PRIMARY-redirect.analysis.windows.net
Activity ID: 41372e9f-288c-4a42-9398-f9db7a424294
Request ID: 1158f7ae-68e5-b3bb-0fab-edf509d283f3
I know that you said that this solution might couse preformance issues but it is weird to me that it can refresh in desktop in about 10 minutes and in service after about 30 minutes I get the error. The report is also rather small: 8 MB and main fact table has 130k rows.
What can I do to solve this?
Is there a way to do this column in Power Query?Hey Saap ,
of course, you can do this in Power Query, basically, each calculated column can be translated to M. You have to be aware that an automatic refresh is also creating queries against the source and this query will not fold. If you are accessing a relational source you can consider asking the owner of the source to add the column or create a custom SQL statement using windowing functions.
In the meantime, I learned some new tricks, and I'm using currently the approach outlined in this article: The previous value - Mincing Data - Gain Insight from Data (minceddata.info)Hopefully, this provides some ideas on how to tackle your challenge.
Regards,
Tom
Hello, I would like to do the same with Power Query. Is that possible?
Thank you
Hi,
This calculated column formula should work
=CALCULATE(MAX(Data[Date]),FILTER(Data,Date<EARLIER(Data[Date])))
Hope this helps.
