I'm replicating an existing excel report on survey data and one section shows percentage of "exceptional" responses from yesterday's results, the average for the last seven days, and the average for the entire dataset (dataset always contains all responses for the last 33 days). I already have a calculated measure that returns exceptional percentage by date.
The expected output is a simple grid like this:
Yesterday: 90%
Last Seven Days: 83%
33 Days: 85%
Seems simple enough. I created a calculated field to group the dates but of course that groups the dates which means "Last Seven Days" does not include "Yesterday" and "Last Seven Days" doesn't include the other two totals.
Here's my group calculation:
If DATEDIFF('day', TODAY(), [Date]) > -2 Then 'Yesterday'
ELSEIF DATEDIFF('day', TODAY(), [Date]) > -8 Then 'Last Seven Days'
ELSEIF DATEDIFF('day', TODAY(), [Date]) > - 34
Then' Current Month'
Else Null
End
If I couple this with the record counts, I get the following result:
Current Month: 136,911
Last Seven Days: 29,080
Yesterday: 4,642
How can I get this to return:
Current Month: 170,633
Last Seven Days: 33,722
Yesterday: 4,642
I feel like it's simple but it's eluding me and I can't really afford to spend much more time on it.
Thanks!
I've attached a sample packaged workbook and a screen shot of the existing excel report. The existing report uses multiple pivot tables to populate a single table that the report is referencing. The new tableau report is linked to the datasource, which updates daily, which is why the calculations with relative dates need to be dynamic.
