Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bknecht2
Frequent Visitor

Issue with DAX Formula - Can't Figure it Out

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))) 

 

1 ACCEPTED 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)

 

View solution in original post

4 REPLIES 4
lbendlin
Super User
Super User

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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors