Summing profit & loss whilst maintaining stock value for balance sheet with a single day slider filter

Being a finance guy by background, this question from the forums got me straight away. The request was to find a solution where a single value slider (this is important) filter could be used on a daily basis but when the “All” function was used, it should only sum up the values for the profit & loss item (such as sales) but return the value of the last day of the month for the balance sheet item(s).

Which makes sense. On a daily level, we can just use whatever the result is for both parts but when “all” is selected, we only want the overall monthly sum for profit & loss items as P&L is a flow value whereas a balance sheet item is a stock value.

Let’s get this done – with table calculations

As apparently in all my blog posts so far, what we will be making use of are table calculations.

There is only one situation that we need to handle and that is the “All” filter. In that case, we have multiple days selected at once and thus need to get rid of all the days for the Balance Sheet item and only consider the last day. How do we do that?

Start with putting your [Date] field on rows, order ascending (for our filter). Then we will be finding out what is the max date IN the view:

// Max Date in View
window_max(max([Date]))

Now we define to use the balance sheet figure only per the last date

// TC | Balance Sheet field at Max Date
if max([Date]) = [Max Date in View] THEN sum([Balance Sheet field]) END

Finally, we have to remove those days from the view that are not the last day. Otherwise we would see a long list of dates with no values. So create this field and put it on the filter shelf. Leave it to compute table down.

//Filter 
last() = 0

And that is it. The second calculation will only be true for the last day of the month so if “all” is selected, the last day is returned. If any other specific day is selected, since we filter, the window_max will always be also the max of the date so its true and the value is returned.

The filter then is for optics.

Find the workbook here https://public.tableau.com/app/profile/steffen2460/viz/Filteralltoshowsameaslastdayofmonth/Dashboard1

And that is it.

As always, appreciate your feedback.

Until next time

Steffen