I am trying to create a metric which looks at our customers account balances over time, however, we want the balances to aggregate differently to any of the native options which Tableau allows. The balance should always show the latest available date in that time period, for example when looking at August 2018, it should show the data from August 31st 2018. When looking at Q3 2018, it should show the data from Sept 30th 2018. Aggregated at the year level should always show Dec 31st, etc.
I have tried a tip which I picked up in a session at TC18, by hard-coding the aggregation as part of the metric, for example:
IF COUNTD(DATEPART('quarter',[Date])) = 1 THEN
WHEN 3 THEN [Balance]
WHEN 6 THEN [Balance]
WHEN 9 THEN [Balance]
WHEN 12 THEN [Balance]
ELSE NULL END)
ELSEIF COUNTD(YEAR([Date])) = 1 THEN
SUM(IF Month([Date]) = 12 AND DAY([Date]) = 31
ELSE NULL END)
... which works fine for historical data, but if we are currently in a period, we want it to show the latest available data. For example, today (Nov 20th 2018), November (month-level), Q3 (quarter-level) and 2018 (year-level) should all be showing the data from November 19th as that is the latest we would have available.
To add one further complication, I would like to use this metric as part of a data source which we can then give to less-experienced users to create simple reports. We want it to be "drag-and-drop" easy, so users don't need to go into calculations and change them to get them working, so I would like to avoid using a table calculation (e.g. FIRST() or LAST()) as part of this to avoid future complications for users.
I've created a very simple dummy data set along with an example of what the final solution should look like.
Thanks in advance for any help!
Tableau Date Issue Sample.xlsx 65.7 KB