Find previous date for given value

[tlong]

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!

[11097486]

Hello, I would like to do the same with Power Query. Is that possible?

Thank you

[mwegener]

Hi @tlong, 

has your problem been solved?

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[Ashish_Mathur]

Hi,

This calculated column formula should work

=CALCULATE(MAX(Data[Date]),FILTER(Data,Date<EARLIER(Data[Date])))

Hope this helps.

[TomMartens · Accepted solution]

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

[Saap]

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?

[TomMartens]

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

[mwegener]

Hi @tlong 

try this

Last =
CALCULATE (
    MAX ( 'Table'[Date] ),
    FILTER ( ALL ( 'Table'[Date] ), 'Table'[Date] < MAX ( 'Table'[Date] ) )
)

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.