Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
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
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;