DAX Measure to find date of previous record for each piece of data in a time series - HELP

Anything related to PowerPivot and DAX Formuale
stuartbisset
Posts: 1
Joined: Fri Jan 12, 2018 8:21 pm

DAX Measure to find date of previous record for each piece of data in a time series - HELP

Postby stuartbisset » Fri Jan 12, 2018 8:23 pm

Hi folks
I am trying to write a DAX measure that will return the date of the previous piece of data in a time series. See the example below (UK date format dd/mm/yy):

FACT TABLE
Date Value PreviousDate
01/03/17 100
03/04/17 200 01/03/17
09/06/17 175 03/04/17
15/06/17 250 09/06/17
...
(PreviousDate is not part of the FACT table. The first 2 cols are the FACT table. The 3 cols together represent the format of my required output report)


CALENDAR TABLE
Date
01/01/17
02/01/17
03/01/17
...

1 to many relationship between Calendar[date] and Fact[Date]

measure Val:=Sum(Fact[Value]) - this works fine


My Attempts:
PreviousDate:=CALCULATE(MAX('Calendar'[Date]),FILTER('Calendar',[Val]<>0)) - this does return a value but it returns the same date as the date of each record. So I tried to exclude the date of the record from the MAX calc, as follows:

PreviousDate:=CALCULATE(MAX('Calendar'[Date]),FILTER('Calendar',[Val]<>0),DATESBETWEEN('Calendar'[Date],MIN('Calendar'[Date]),DATEADD('Calendar'[Date],-1,DAY))) - but this throws an error

I have tried a couple of other variants, including using FILTER instead of DATESBETWEEN and I even had an EARLIER statement in there at some point but I just can't work it out.

Any help would be greatly appreciated.

Thanks in advance
Stuart
(Excel 2016)

MattAllington
Posts: 919
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: DAX Measure to find date of previous record for each piece of data in a time series - HELP

Postby MattAllington » Sat Jan 13, 2018 7:08 am

How about doing this in Power Query on load. Watch my video here https://www.youtube.com/watch?v=xN2IRXQ2CvI
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 9 guests

cron