How do we calculate difference and percent difference between two columns in tableau

0
votes

]Difference and percent Difference must be calculated.

I cannot do Apr20-MAy20 because it is not always the same. I need to show the current month and previous month

So I did a relative filter to just show the current month and previous month. So the difference of two columns should automatically change when the month changes.

enter image description here

Now how do I get the same month of prior year, how do I filter ?

I also need to calculate the difference of current year same month and previous year same month.

Thank you in advance for any help!

When I do table across difference, the difference value is overwriting the existing May and Apr month values as the below screen shot, how to show the difference in another column

Currently: enter image description here

Below is Expected:

enter image description here

1
tableau-api

1 Answers

0
votes

Sounds like you should create a custom filter for the dates. You want:

  • This month this year
  • This month last year
  • Last month this year

There are a number of ways you could do this. I'll give one example and will assume there aren't any future dates in your data set.

[DateFilter]: DATETRUNC('month',[YourDateField])>=DATETRUNC('month',DATEADD('month',-1,TODAY())) OR DATETRUNC('month',[YourDateField])=DATETRUNC('month',DATEADD('year',-1,TODAY()))

Put the to the filters shelf, set to True, and it should keep the months you want.

Then you can just use the standard table calculations to calculate Difference and Percent Difference.

Note, the formula isn't tested, just typed directly into here, let me know if it doesn't work

Based on your comments look at creating separate calculations for to YoY / MoM / etc calculation. That also means creating calculated fields to isolate the Current Month, Previous Month, etc.

For example, the current month:

[isCM]: DATETRUNC('month',[YourDateField]) = DATETRUNC('month',TODAY())

The previous month:

[isPM]: DATETRUNC('month',[YourDateField]) = DATETRUNC('month',DATEADD('month',-1,TODAY()))

Then month on month, something like:

[MoM]: (SUM([Measure])*INT([isCM]))/(SUM([Measure])*INT([isPM]))

To make your table check this article about using the placeholder technique to create tables in Tableau