Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hey Everyone,
I'm having an issue getting the right answer from a DAX formula and hoping someone can help me out - been trying for days now (I have an example data file attached with the answer I'm hoping to get).
Esentially, I have a slicer that is the active date relationship between my date and data tables. I'm trying to create a simple SUM measure that sums up the data for the same time period last year using a different date on my data table (an inactive date relationship). Up to this point I'm fine. Where I am running into issues is, I ALSO want to limit the results to rows where the active date relationship is within the slicer date ranges (minus the 1 year).
Below is the measure I'm writing and coming up with BLANK. Attached is an example data file that may explain my issue in a better way and has the result I'm hoping to get.
Any help here would be GREATLY appreciated.
Link to Example File: https://www.dropbox.com/s/plhogyjmkotbg7m/DAX%20Issue%20Example%20Data.pbix?dl=0
TQO's Prior Year =
CALCULATE([TQO's],
SAMEPERIODLASTYEAR('Date Table'[Date]),
USERELATIONSHIP(Data[TQO Date],'Date Table'[Date]),
FILTER(Data,(Data[AQL Date])>=(MIN('Date Table'[Date])-365)))
Solved! Go to Solution.
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)
You say you want it within the slicer range but then you use a different value for the MIN computation?
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?
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)
@lbendlin - This works perfectly, thank you! I spent hours on this before - very much appreciated.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
16 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |