Daily cumulative value for yesterday's date (in a table)

[jbchurchill]

I've got a column of Cumulative Daily Values with IMPORTANT_DATE in a format like "2021-03-28 15:00:00.000". The time stamp appears to always be the same (there is only one value for each date anyway).

=Only({<[IMPORTANT_DATE]={"Date(Today( 0)) - 1"}>} [Cumulative_DailyValue])

I'm trying  to isolate the single value for yesterday's date. I am doing it in a KPI currently (and it is NOT working) but ultimately I want to use this in a table that is filtered further to a value for each county in our state. I already have a field in the table for the county field that does this.

=If(not Match(County, 'Unknown', '', '-'), County)

That part works great and I figure that (I am hoping anyway) once I get the KPI to show the value, the table will work for each county's daily value for yesterday's date.

[morgankejerhag · Accepted solution]

The format of the date in the field and in the comparison in the set analysis must be exactly the same. To simplify I would add a field in the script as:
if(floor([IMPORTANT_DATE])=(today(0)-1),1,0) as FlagYesterday

Then in the set analysis just check FlagYesterday={1}

[jbchurchill]

Thanks for the reply. I'm not sure where to put that in the "script". Are you referring to the data load script? It doesn't completely validate there (needs ";" at the end of the line and even then shows "garbage" after "as" so I'm not sure where this fits in. Sorry but my understanding of this just isn't quite there yet.

[morgankejerhag]

Yes, the data load script. It should be in the load statement that reads the table where IMPORTANT_DATE is located.

[jbchurchill]

Sorry to drag this out but I noticed that this is already in there. Perhaps I put that line in there right after this? or should I just use [Date]?

date(floor([IMPORTANT_DATE])) AS [Date];