Add a column that shows previous year, YTD.

Hi Ricky Melamed

Taking my assumption above as true, there are numerous ways you can achieve this, here's one of them.

Create a new Formulate calculation against your model and drag the "Current Period" block onto the canvas and assign your Date hierarchy to it. I'm assuming you have a Date hierarchy otherwise you can't do Year to Date at all! The Current Period block will take the system date (today) and return the member of the assigned hierarchy that matches. So if it is 19th April 2025 and the assigned hierarchy is Date, it will return the 2025-04-19 member of the Date hierarchy. If you used say Full Month Name, it would return Apr 2025, etc.

But we want the same day last year. Use the function ParallelPeriods. This will return the same date for the previous year, the numeral defining how many years to go back. (It can do a whole lot more see Help for more details. Also note that there are two functions, ParallelPeriods, for use with flat time hierarchies, and ParallelPeriod, for use with time based regular hierarchies.)

We bow have the same date last year. Add the YTD function. This will return all the dates from the beginning of the year up to and including the date selected.

Now use Aggregate to aggregate the dates to a total. It's always best to use Aggregate here as it will respect any underlying calculation of the Measures used, rather than generate a simple Sum.

Save the calculation into your Year hierarchy using the drop down on the right to select it.

Add the calculated member into your grid. It will appear after all the "real" members. To place it in the correct order, right clock on the Year hierarchy and select custom order:

Select the custom member and use the up/down arrows to move it to the correct position and apply.

Mission accomplished!

Hope that helps!

Ian