Forum Discussion

tlong's avatar
tlong
Regular Visitor
6 years ago
Solved

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 gener...
Need Help
Tips and Tricks
  • TomMartens's avatar
    TomMartens
    6 years ago

    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

     

     

TomMartens's avatar
TomMartens
Super User
6 years ago

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's avatar
Saap
Resolver III
4 years ago

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?