1 Reply Latest reply on Jun 18, 2018 8:48 AM by Alex Braun

# Calculation of Table between previous months and years

Everyone, good afternoon!

I gave a search and could not find a forum with this doubt, I apologize if it already has.

I need help calculating the tableau.

I need to return in a table the value of the current month, value of the previous month and value of the month of the previous year, being that in the filter I can select only 1 year.

I'm attaching the workbook and an excel print of how I'd like it to look.

Can someone help me.

Thank you

• ###### 1. Re: Calculation of Table between previous months and years

Depending on weather you want the output to be automatic or manual, this can be done pretty easily.

First, you will create 2 parameters.

The first, I will call Date Type. Here you can set some pre-defined periods. So I will put 3 options.

Current Month

Previous Month

Custom

The second parameter will be a date parameter, you can name it whatever you would like.

Next you will need to create a calculated field to give you the reference date, the calculation is as follows [Reference Date]

if [Date Type] = 'Current Month' then datetrunc('month',Today())

elseif [Date Type] = 'Previous Month' then dateadd('month',-1,datetrunc('month',Today()))

else datetrunc('month',[Date Parameter])

end

The next calculated field will be for our filter. [Date Filter]

if [Reference Date] = datetrunc('month',[Data Date]) then 'T'

elseif dateadd('month',-1,[Reference Date]) = datetrunc('month',[Data Date]) then 'T'

elseif dateadd('year',-1,[Reference Date]) = datetrunc('month',[Data Date]) then 'T'

else 'F'

end

Then apply the [Date Filter] and accept 'T' to get these.  You can also create 3 separate calculated fields that instead of 'T', you put the period name for your month,month-1 and month previous year headings.

I have added a file with the calcs and applied the filter.