stuartbisset
New Member
- Joined
- Jan 12, 2018
- Messages
- 4
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)
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)