Hi Joe,
Can you tell me how I can include a date dimension in the below to look at last year(2018) and rolling same months?
WINDOW_SUM(SUM([Sales]),-11,0)
will add up all the SUM([Sales]) values from 11 previous marks plus the current one.
WINDOW_sum(sum(sales), FIRST(), 0)
will add up from the first through the current.
What I am currently using to calculate the YTD value is this. But this is only working for this year to date and I need the same to find a total sum for last year.
WINDOW_SUM([Reported Value]
,last()-window_min(if max(datepart('month',[ApplicableDate]))=4
then last() end,first(),last()),LAST())
Regards,
Rani
Can't open your workbook. Your data source is on your server:
This article tells you how to fix that:
Do the section for "Create a .twbx with Tableau Server data sources"
But I can see that your filer calc is getting rows only within the date range. So the prior year data is not there for you.
What you can do is make your current-year calc grab rows within the range for the current year, and make your prior-year calc grab rows within the start-date-minus-a-year and end-date-minus-a-year. And don't filter rows, but just report on those new calcs.
Or, another thing you could do is make the [Within Date Range] a table calc. When you filter with a table calc, the underlying table is not filtered. It remains intact. So something like this:
LOOKUP( ATTR([Within Date Range]) , 0 )
(So leave your original [Within Date Range] calc as it is.)
Put that on filters. You'll get the same sort of filter look-and-feel, and you'll be able to select for value = 1. That's all that will display. But the rows outside the date range will still be in the underlying table for you to do LOOKUP or other table calc access to get the prior year data.
