cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tlong
Regular Visitor

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:

DateValueDesired Result
1/1/2019Doug 
1/2/2019Doug1/1/2019
1/8/2019Doug1/2/2019
1/4/2019Ben 
1/7/2019Ben1/4/2019
1/10/2019Ben1/7/2019

 

Thanks in advance!

1 ACCEPTED SOLUTION
TomMartens
Super User
Super User

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:
image.png
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

 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

7 REPLIES 7
11097486
Helper I
Helper I

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

 

Thank you

mwegener
Super User
Super User

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.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


Ashish_Mathur
Super User
Super User

Hi,

This calculated column formula should work

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

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
TomMartens
Super User
Super User

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:
image.png
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

 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
mwegener
Super User
Super User

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.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors