topic Re: Last year same month (MTD) in SAS Visual Analytics

Last year same month (MTD)

Melissak — Thu, 13 May 2021 13:50:48 GMT

Hi,

How to calculate the month to date period for the same period last year.

Eg. If today is 13/05/2021 (Current Month to Date) how do I calculate what sales was for 1/05/2020 to 13/05/2020?

Re: Last year same month (MTD)

Sam_SAS — Thu, 13 May 2021 14:07:42 GMT

Hello,

You would use the ParallelPeriod operator in a calculated item.

Here is an example that should calculate the month-to-date value for the previous year:

ParallelPeriod(_Sum_, 'Expenses'n, _ApplyAllFilters_, 'Date'n, _ByMonth_, _ByYear_, -1, _ToDate_)

The last parameter above specifies the "to date" part.

The UI for the periodic operators in the graphical expression editor is a bit confusing, so refer to the doc and the tooltips.

Let us know how it goes.

Thanks,
Sam

Re: Last year same month (MTD)

Melissak — Fri, 14 May 2021 10:22:54 GMT

Hi
I tried the above but it does the same calculation for every year in the data.
I want it to only return that value for the one month. (Last year, this month)

Re: Last year same month (MTD)

Sam_SAS — Fri, 14 May 2021 19:08:10 GMT

Getting only a specific month is actually a bit trickier.

The easiest way might be to precalculate the value in your source table.

I am making some inquiries about a way to do it within VA.

Sam

Re: Last year same month (MTD)

Melissak — Thu, 20 May 2021 12:54:18 GMT

I managed to do this with the calculated item below:

IF ( ( Month('DATE X'n) = Month(DatePart(Now())) ) AND (
Year('DATE X'n) = Year(DatePart(Now()))-1 ))
DayOfMonth('DATE X'n) <= DayOfMonth(DatePart(Now())) ) )
RETURN 'VAUE X'n
ELSE 0

Re: Last year same month (MTD)

Sam_SAS — Thu, 20 May 2021 14:53:24 GMT

Great! Thanks for sharing your solution 🙂