Tableau -- Change calculated field based on DateDiff('month', [date], Today())

Question

I have a calculated field Recent Price Change set up as follows:

IF DATEDIFF('month', [Posted Date], TODAY()) == 0
THEN [Price Change]
END

This is supposed to hold price changes for only the most recent month in my dataset, which is updated monthly. Unfortunately, for the first week of the month, before the new data has been added, there are no rows where DATEDIFF('month', [Posted Date], TODAY()) == 0 evaluates to true. Currently I have replaced this condition with DATEDIFF('month', [Posted Date], TODAY()) <= 1 but I really only want this for the single most recent month.

I tried making a new calculated field Same Month like...

IF DATEDIFF('month', [Posted Date], TODAY()) == 0
THEN TRUE
ELSE FALSE
END

but this didn't work for obvious reasons. I suppose I want this as more of a global variable that will be true if any [Posted Date] satisfies DATEDIFF('month', [Posted Date], TODAY()) == 0, rather than a calculated field but I don't know if this is possible in Tableau.

One other related question. I am calculating prices for certain parts that change from month to month. I would like to be able to filter by this Recent Price Change column. If I filter it now, say for [Recent Price Change] > 10, it only removes price data for the most recent month (or two based on my temporary fix.) What I would like is to filter out all parts where the recent price change was more than some dollar amount, let's say $10 as an example.

So for instance, for the following two parts, all part A and C records should be filtered off while all part B records should remain.

part     price     price change     recent price change     date
A        52        2                2                       2019-02-01 00:00:00.000
A        50        0                null                    2019-01-01 00:00:00.000
A        50        0                null                    2018-12-01 00:00:00.000
B        79        31               31                      2019-02-01 00:00:00.000
B        48        6                null                    2019-01-01 00:00:00.000
B        42        0                null                    2018-12-01 00:00:00.000
C        51        0                0                       2019-02-01 00:00:00.000
C        51        39               null                    2019-01-01 00:00:00.000
C        12        0                null                    2018-12-01 00:00:00.000

Perhaps my setup is incorrect. I'm open to any ideas.

Answer 1

In order to fix your first question, instead of anchoring the calculation on today's date, use the max date of your data. You can do this with a Level of Detail calc.

max_date:

{max(Posted Date]}

The use:

IF DATEDIFF('month', [Posted Date], max_date) == 0
THEN [Price Change]
END

For your second question, again use a LOD calc to fix the recent price change for all dates of each part. You could make the 10 in this example a parameter so the user can decide the cut off.

{fixed [Part] : sum([Recent Price Change])}>10

This will apply the T/F to all dates.