Add a column that shows previous year, YTD.
I have sales for a few years in a grid, but want to add a column between 2024 and 2025 that shows the 2024 YTD. I assume that this can be done using time intelligence or formulate, but cannot figure out how. Need some guidance please.
8 replies
-
This is what I have... incase it matters
-
Hi
I'm assuming you mean the year to last year same date as this year?
So today, the YTD 2024 would be the cumulative value up to 18th April 2024? I.e., the same date as today, but back a year, correct?
Ian
-
Hi
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
-
This worked so well... until I added it to present. When I did, the order changed so that 2024YTD is after 2025. Also, in the slicer it is at the end of the list. For the slicer I tried to make a list in formulate, but even then, the 2024YTD shows up at the end of the list.
Suggestion?
-
Hi
It works fine for me:
Make sure you've saved your Discover before putting it on the Present slide. For the list, select your Years individually in the Standard List block (don't use the "All" checkbox), then choose the 2024 - YTD custom member, then use the ordering option in the element panel to correctly place it.
Hope that helps.
Ian