Tableau calculated measure using previous months

0
votes

I have the following data table:

Month Accounts Sales
Jan-19 50 5000
Feb-19 60 6000
Mar-19 70 7000
Apr-19 80 8000
May-19 90 9000

I am trying to create a new measure which will return the sum of 3 months of sales / Sum of 1st month Accounts.
For e.g.
for Mar-19 the value should be (Jan+Feb+Mar'19 Sales)/Jan-19 Accounts i.e. (18000/50)
for Apr-19 the value should be (Feb+Mar+Apr'19 Sales)/Feb-19 Accounts i.e. (21000/60)
for May-19 the value should be (Mar+Apr+May'19 Sales)/Mar-19 Accounts i.e. (25000/70)
.......
and so on...

Was wondering can a DATEDIFF or some table calculation could be use to achieve the above?

Best Regards

1
tableau-api

1 Answers

1
votes

Table calculations are well suited for this. They take a little time to understand but are very useful. Start with the online help. Make sure you understand partitioning and addressing.

The functions that will be useful are Window_Sum() and Lookup()

An example calculation could be

WINDOW_SUM(Sum([Sales]),-2,0) / LOOKUP(Sum([Sales],-2))

The -2 and 0 are offsets from the current position

Note, for table calcs, the formula is only part of the definition of the calculation. You need to edit the table calc to set the partitioning and addressing (aka compute using) to tell Tableau how to arrange the data before evaluating the table calc.

Tableau will take a guess for partitioning based on how your viz is arranged, and the guess is often right, but it is usually best to specify the specific dimensions for partitioning. See the help pages on table calcs.