Hi - I want to create a table or pivot table that has month name or number in one column, one period, in the next column, and another period in the third column. Both number columns can be adjusted by whichever YTD month I want to view. I’ve been able to accomplish this if I’m only viewing one period, but two periods within the same visual I can’t figure out. I’m trying to achieve both of the red outlined areas in the attached screenshot. Any help is greatly appreciated!
Hi,
you can use the running function.
runningSum(sum({Amount}), [truncDate(“MM”,{Date}) ASC], [{qs_year}])
Regards,
Naveed Ali
Hi @jdorbish
Are you using periodOverPeriodLastValue? You could use that to pass MONTH as the period, and the offset as the difference between the months that you want to show.
There are examples in this article that you might be able to adapt to your purpose by changing the period and offset. If you’re saying that you’re choosing the month numbers on the fly, then you can use parameters in that calculation too.
I’m already very familiar with that article you wrote! So yes, I want to choose the month on the fly. I’ve recreated your calculations from that previous article. How would I go about changing the month number via a parameter and how would I incorporate that into the formula for it to update accordingly? Thanks so much for your help!
Hi @jdorbish
I wonder if the periodToDateSum function would be better if you want to be able to see the sum to a certain point in time, since that allows you to specify an end date. You would then be able to see the sum up to the last quarter, for the last 12 months, up to a specific month for example, based on the different scenarios.
If you have a specific number of scenarios you might add them as a drop down for the user to pick, then calculate the end date based on their selection to pass to the function. You may want to calculate this value first, and then pass it to the function rather than trying to add all logic in a single calculated field.