
1. Re: Last Date a measure had a given value
venkatram Oct 30, 2018 5:59 PM (in response to Jhobe Steadman)Create sample file like below,
Drop Cacl3 in the filter and unselect all the date,and apply latest date.
Final results:
Link for your reference.
https://public.tableau.com/profile/dataplusscientist#!/vizhome/LatestValue/Sheet7?publish=yes

2. Re: Last Date a measure had a given value
Jhobe Steadman Oct 31, 2018 9:55 AM (in response to venkatram)First, thank you very much for your reply, I appreciate it.
Unfortunately, my “calculation2” is an average so my version of your Calculation2 “IF = 0 THEN END” gives an error: “Cannot mix aggregate and nonaggregate comparisons or results in “if” expressions”
I tried the 2step approach of assigning a # to the “IF ….” Calculation and then another measure “IF = # THEN END” but that gives the same error as above.
If you have a workaround for this problem I would of course appreciate your help.
Best regards

3. Re: Last Date a measure had a given value
venkatram Oct 31, 2018 12:43 PM (in response to Jhobe Steadman)For better understanding your requirement, we need a sample data dump or your working file with .twbx format.

4. Re: Last Date a measure had a given value
Jhobe Steadman Oct 31, 2018 2:52 PM (in response to venkatram)I have >1.5M row of data and creating a sample data set would take hours. Does the following description help (I have substituted placeholder names for the actual variable, measure & parameter names) :
Dimension: Every date for the last 3 years
Dimension: Test result for every test (multiple test results per day)
Measure: #Tests for every date
Measure “AvgX”: Window_Avg(Sum(Test results)) for every date
Measure “AvgY”: Window_Avg(Sum(#Tests)) for every date
The following measures:
ExcludeDay
IF >= AND > THEN END (Note: I can’t put a date in where ParamZ is or I get the “cannot mix aggregate and nonaggregate comparisons or results in “if” expressions” error)
LastExcludeDay
IF = THEN Max() END (This still gives the error above.)
Again, the goal is to get the date of the last time that equaled ParamZ, preferably relative to a date that I put in another parameter
Thank you

5. Re: Last Date a measure had a given value
Jhobe Steadman Oct 31, 2018 3:17 PM (in response to venkatram)Apparently, any of my text that has square brackets around it is being lost so I am resending my reply with all square brackets removed.
I have >1.5M row of data and creating a sample data set would take hours. Does the following description help (I have substituted placeholder names for the actual variable, measure & parameter names) :
Dimension: Every date for the last 3 years
Dimension: Test result for every test (multiple test results per day)
Measure: #Tests for every date
Measure “AvgX”: Window_Avg(Sum(Test results)) for every date
Measure “AvgY”: Window_Avg(Sum(#Tests)) for every date
The following measures:
ExcludeDay
IF AvgX >= ParamX AND AvgY > ParamY THEN ParamZ END (Note: I can’t put a date in where ParamZ is or I get the “cannot mix aggregate and nonaggregate comparisons or results in “if” expressions” error)
LastExcludeDay
IF ExcludeDay = ParamZ THEN Max(Date) END (This still gives the error above.)
Again, the goal is to get the date of the last time that AvgX equaled ParamZ, preferably relative to a date that I put in another parameter
Thank you

6. Re: Last Date a measure had a given value
Dan Cory Feb 11, 2019 5:55 PM (in response to Jhobe Steadman)I just stumbled on this fairly old question. I'm not sure if you still need an answer.
I'm not sure why you were using Window_Avg but that has a lot of impact on the answer.
My guess is that you don't need the window_avg and just need level of detail expressions:
AvgX: {FIXED [Date]: AVG([Test Results])}
AvgY: {FIXED [Date]: AVG([#Tests])} // I'm not sure what you are computing here
I'm not sure what you are doing with ExcludeDay and LastExcludeDay.
The date of the last time AvgX equals ParamZ is:
MAX({FIXED [Date]: IF AvgX = ParamZ THEN [Date] END})
If that's really all you need, then you may be able to write it as a single level of detail expression:
MAX({FIXED [Date]: IF AVG([Test Results]) = ParamZ THEN [Date] END})
Dan