topic Re: Finding previous date's value in one column based on another column in DAX Commands and Tips

Finding previous date's value in one column based on another column

Anonymous — Thu, 09 Apr 2020 16:57:33 GMT

Hello everyone,

I really got stuck with this one. I can't find any help for the PREVIOUSDAY function. I need to add a column with the previous date's value for a certain value in another column. My dates are not continuous. Example:

Segment Date UniqueOpenRate
WiFi 1/3/2020 15%
Hotel concierge 1/3/2020 14%
WiFi 2/3/2020 16%
Hotel Concierge 2/3/2020 13%

I need to be able to add a new column where I set the previous value for each Segment:

Segment Date PrevOpenRate
WiFi 2/3/2020 15%

How can I do that?
Thanks!
Stan

Re: Finding previous date's value in one column based on another column

az38 — Thu, 09 Apr 2020 17:24:30 GMT

Hi Anonymous

try Measure

Measure = var _curDate = MAX('Table'[Date]) var _prevDate = CALCULATE(MAX('Table'[Date]), ALLEXCEPT('Table','Table'[Segment]),'Table'[Date] < _curDate) RETURN CALCULATE(SUM('Table'[UniqueOpenRate]), FILTER(ALLEXCEPT('Table','Table'[Segment]),'Table'[Date] = _prevDate))

Re: Finding previous date's value in one column based on another column

Anonymous — Thu, 09 Apr 2020 17:52:04 GMT

This works! Thank you so much! Yuu saved me so much time!