Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Good morning,
I have a calculate measure that returns the product from a time period on a slicer. It calculates the last 12 months return for an account. When the dates on the slicer is less than 12 month time period, I want the measure to return a null, N/A, or Blank because there is not enough dates selected for a full 12 month return. I was attempting this via IF statement, but it did not work in DAX.
Current Formula Works when 12 or More Months Selected:
12 Month Return = CALCULATE(product(Table1[Return]]),DATESINPERIOD(Table1[Date],LASTDATE(Table1[Date]),-12,MONTH))
Failed Formula:
12 Month Return = IF(VALUES(
CALCULATE(product(Table1[Return]]),DATESINPERIOD(Table1[Date],LASTDATE(Table1[Date]),-12,MONTH))<12,BLANK(),
CALCULATE(product(Table1[Return]]),DATESINPERIOD(Table1[Date],LASTDATE(Table1[Date]),-12,MONTH))
Any advice or tips? Kindly & Thank you,
James
Solved! Go to Solution.
It sounds like you want to test your IF statement based on the number of months, but your code is looking at the value of the calculation.
VALUES requires a table/column reference. Try the below:
12 Month Return = IF ( DATEDIFF ( FIRSTDATE ( ALLSELECTED ( Table1[Date] ) ),
LASTDATE ( ALLSELECTED ( Table1[Date] ) ), MONTH ) < 12, BLANK (), CALCULATE ( PRODUCT ( Table1[Return] ), DATESINPERIOD ( Table1[Date], LASTDATE ( Table1[Date] ), -12, MONTH ) ) )
Hope this helps
David
It sounds like you want to test your IF statement based on the number of months, but your code is looking at the value of the calculation.
VALUES requires a table/column reference. Try the below:
12 Month Return = IF ( DATEDIFF ( FIRSTDATE ( ALLSELECTED ( Table1[Date] ) ),
LASTDATE ( ALLSELECTED ( Table1[Date] ) ), MONTH ) < 12, BLANK (), CALCULATE ( PRODUCT ( Table1[Return] ), DATESINPERIOD ( Table1[Date], LASTDATE ( Table1[Date] ), -12, MONTH ) ) )
Hope this helps
David
Thanks for your response. The formula you provided seems close. The formula works if I hardcode dates in the following spot of:
FIRSTDATE ( ALLSELECTED ( Table1[Date] ) ),
LASTDATE ( ALLSELECTED ( Table1[Date] ) ),
However, the formula above reads every DATEDIFF interval in my date column. I've tried many iterations but to not avail. Should I have a different setup with a date column or calendar table? Not sure how to best troubleshoot this.
Thank you, kindly,
JK
DATEDIFF Month is kind of "tricky" to use
To see what I mean try it on 1/1/2016 to 12/31/2016 - you would expect to get 12 but you actually get 11
Try this instead
12 Month Return = IF ( DATEDIFF ( FIRSTDATE ( Table1[Date] ), LASTDATE ( Table1[Date] ), DAY ) < 365, BLANK (), CALCULATE ( PRODUCT ( Table1[Return] ), DATESINPERIOD ( Table1[Date], LASTDATE ( Table1[Date] ), -12, MONTH ) ) )
I notied the discrepency within DATEIFF regarding 11 vs 12 months for one year. I adjusted my formula accordingly, but it's still confusing. I'll study it within the column context you visualized below. Thank you for your help.
Creating a separate calender table, creating a relationship between the two and then using Date from the calendar table (in the measure) is best practice (also, use the calendar table as the source for the slicer). That should allow the filters to propogate correctly.
Thanks for the quick response. I was able to get it to work, thanks to you. I used an alternative method but it worked as well. I used MIN/MAX within DATEDIFF.
DATEDIFF (
MIN(Table1[Date] ),
MAX( Table1[Date]) ,
MONTH
)
Thanks again,
JK
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |