Prior year set analysis based on date column

[uko223]

Hello, 

I have a table that I need current year amount and prior year amount on. I have a date column to drive the measures. The first measure is fine as the date column drives the Actual amount. However, for prior year, I need the amount to be driven on the date column minus 365. So 2/26/2019 - 365, 3/1/2019 - 365, etc. How can I accomplish this?

Date	Actual	Prior Year
2/26/2019	$20,025	$0
3/1/2019	$23,816	$0
2/25/2019	$13,246	$0
2/27/2019	$14,021	$0
2/28/2019	$23,697	$0
3/2/2019	$126	$0
2/24/2019	$6,247	$0

[tincholiver · Accepted solution]

if you put as dimensions month, day and year in a pivot table will give as a result which were the sales for a given day of the year and the respective day of the previous year. if you want to use set analysis you would have to make two expressions one for the current year and one for the previous year:
sum ({<Year = {'> = $ (= max (Year))'}>} Sales)
sum ({<Year = {'<= $ (= max (Year) -1)'}>} Sales)

I do not know if it's the result you're looking for. tell me if that solution serves you

[martenlouisse]

You could it arrange it your script with something like this:

Table:
LOAD
Date,
Actual
Resident YourTable;

Concatenate

LOAD
Date + 365 as Date,
Actual as [Prior Year]
Resident YourTable
Where Date <= Date(Today())-365;

[uko223]

Thank you!