Forum Discussion
Issue with DAX Formula - Can't Figure it Out
TQO's Prior Year = var d=min('Date Table'[Date]) var e=EDATE(d,-12) var c=CALCULATE([TQO's] ,SAMEPERIODLASTYEAR('Date Table'[Date]) ,USERELATIONSHIP(Data[TQO Date],'Date Table'[Date]) ,Data[AQL Date]>=e) return cNote: This give you the desired result but it ignores the MAX value of the date slider.
Note 2: Your 365 made me chuckle. Replaced it with the much safer EDATE(-12)
Hi There,
I do want it within the slicer range (the 'Date Table'[Date]), but minus 1 year, hence the -365 days of the minimum date selected on the slicer. So esentially, if the slicer date range on the example file is 1/1/2020 to 4/5/2020, I want this measure (TQO's Prior Year) to calculate the TQO's where the TQO date is between 1/1/2019 to 4/5/2019 and ALSO WHERE the AQL date is between 1/1/2019 and 4/5/2019. I can write the measure to work up until that last part, where the AQL date is also between 1/1/2019 and 4/5/2019.
The measure that gets me there without that last part is:
TQO Prior Year =
CALCULATE([TQO's],
SAMEPERIODLASTYEAR('Date Table'[Date]),
USERELATIONSHIP('Campaign Member'[FCRM__FCR_SQR_Date__c],'Date Table'[Date]))
Does that make sense?
Super User
TQO's Prior Year =
var d=min('Date Table'[Date])
var e=EDATE(d,-12)
var c=CALCULATE([TQO's]
,SAMEPERIODLASTYEAR('Date Table'[Date])
,USERELATIONSHIP(Data[TQO Date],'Date Table'[Date])
,Data[AQL Date]>=e)
return c
Note: This give you the desired result but it ignores the MAX value of the date slider.
Note 2: Your 365 made me chuckle. Replaced it with the much safer EDATE(-12)