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.
Hi,
I'm trying to filter the values in a table by the second most recent date. So, from the table below the formula would output '4', because it is associated with 4-Jan.
I am trying to use filter the rows by the appropriate date so it returns the correct value. However when I try to nest the FILTER expression in my calculation, it returns a blank value. I can't include the actual data, but the formula I've got now is:
Date | Value | |
01/01/2020 | 1 | |
02/01/2020 | 2 | |
03/01/2020 | 3 | |
04/01/2020 | 4 | |
05/01/2020 | 5 |
Solved! Go to Solution.
I just tried the measure on your test data and it works as expected. What error are you getting exactly?
2nd Recent Value =
var thisdate= max('DATA SHEET ACTUALS'[Date])
var seconddate = CALCULATE(max('DATA SHEET ACTUALS'[Date]),'DATA SHEET ACTUALS'[Date]<thisdate)
return CALCULATE(SUM('DATA SHEET ACTUALS'[Value]),'DATA SHEET ACTUALS'[Date] = seconddate)
try this one. Might need some fine tuning.
2nd Recent Value =
var thisdate= max('DATA SHEET ACTUALS'[Date])
var seconddate = CALCULATE(max('DATA SHEET ACTUALS'[Date]),'DATA SHEET ACTUALS'[Date]<thisdate)
return CALCULATE(SUM('DATA SHEET ACTUALS'[Value]), FILTER(ALL('DATA SHEET ACTUALS'), 'DATA SHEET ACTUALS'[Date] = seconddate))
Thanks for the help, however I think this leads to similar problem, i.e with the 'seconddate' formula you've suggested, it seems you're not allowed to use a measure within the formula to derive a value, so the '<[thisdate]' portion throws an error.
I've built the measure for the seconddate already, it's just deriving the actual value from it that I'm struggling with.
I just tried the measure on your test data and it works as expected. What error are you getting exactly?
2nd Recent Value =
var thisdate= max('DATA SHEET ACTUALS'[Date])
var seconddate = CALCULATE(max('DATA SHEET ACTUALS'[Date]),'DATA SHEET ACTUALS'[Date]<thisdate)
return CALCULATE(SUM('DATA SHEET ACTUALS'[Value]),'DATA SHEET ACTUALS'[Date] = seconddate)
Apologies, user error when entering - working now. Many thanks for your help!
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 | |
27 | |
20 | |
15 | |
12 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |