Hi, what would be your solution if you would want a YTD bar when you're actually not 'complete' in months. Like right now, we're in month complete 3. You want to show 2021, 2021 YTD (till m3), 2022, 2022 YTD and 2023. Is this possible within a pivot without using too many reference cells?
My question is what if you have a column with period data and each period that gets repopulated. How can I capture that data at the end of the period and keep cumulative totals per row.
These get pretty complicated. Is it a situation where Period 1 changes from 5 to 2 to 7 and you want to record 5+2+7 or 14? That requires VBA or Iterative Calculation. It would be easier to solve this if you could start a new post at my website with a picture of the data and then we can try some VBA or iterative calculation. Go here, register as a new member and Post Thread: www.mrexcel.com/board/forums/excel-questions.10/
Thanks. For me the solution was to copy values of period data to a new table with the month as a row. Then I can query / pivot this table for various to date totals.
You and Mike are tabular pivot guys, aren't you? When I look at the 2020 and 2021 values repeating themselves like that, I begin to sweat. It just feels wrong. Especially when there's a descriptive subtotal. I'd have gone compact there. Think of the real estate saved on that screen with two less columns? You could have had a picture of a nice duck or your favourite dinosaur or something.
Do you have that double-secret beta of Excel with =ISINDENTED() and HowManyCharactersIsThatRowLabelIndented() so you can tell each field apart? I can't wait until I get those two functions so I can do something useful with the Compact layout.
@@MrXL power pivot has all those things! Sort of. Before that, it was a lovely VBA function to return the field number. I've never found it too much of a problem to be honest. I guess I've always tried to distinguish between report and data. If someone wants to interact with the report, then they use slicers and filters, but if they want to extract, calculate from or reuse data then they go to the source data. That's exactly who things are in my utopian world you see. Come to think of it, it's been a long time since anyone called me back about prospective work.
Bill,
Great, informative Pivot table trick. This will be very helpful to me!😊
Thanks Mr Excel! Love these quick videos with great info
Game changer, thank you.
Thanks Mr Excel!!!
Thank u Bill 👍
Every day is a school day Bill 👍
This is 👍 great. I can do it thru right click
Nice vídeo! Is there a way to do it backwards? Like, O have sales prices for yearly or quarterly values and I want to show it on monthly values
Hi, what would be your solution if you would want a YTD bar when you're actually not 'complete' in months. Like right now, we're in month complete 3. You want to show 2021, 2021 YTD (till m3), 2022, 2022 YTD and 2023. Is this possible within a pivot without using too many reference cells?
My question is what if you have a column with period data and each period that gets repopulated. How can I capture that data at the end of the period and keep cumulative totals per row.
These get pretty complicated. Is it a situation where Period 1 changes from 5 to 2 to 7 and you want to record 5+2+7 or 14? That requires VBA or Iterative Calculation. It would be easier to solve this if you could start a new post at my website with a picture of the data and then we can try some VBA or iterative calculation. Go here, register as a new member and Post Thread: www.mrexcel.com/board/forums/excel-questions.10/
Thanks. For me the solution was to copy values of period data to a new table with the month as a row. Then I can query / pivot this table for various to date totals.
You and Mike are tabular pivot guys, aren't you? When I look at the 2020 and 2021 values repeating themselves like that, I begin to sweat. It just feels wrong. Especially when there's a descriptive subtotal. I'd have gone compact there. Think of the real estate saved on that screen with two less columns? You could have had a picture of a nice duck or your favourite dinosaur or something.
Do you have that double-secret beta of Excel with =ISINDENTED() and HowManyCharactersIsThatRowLabelIndented() so you can tell each field apart? I can't wait until I get those two functions so I can do something useful with the Compact layout.
@@MrXL power pivot has all those things! Sort of. Before that, it was a lovely VBA function to return the field number.
I've never found it too much of a problem to be honest. I guess I've always tried to distinguish between report and data. If someone wants to interact with the report, then they use slicers and filters, but if they want to extract, calculate from or reuse data then they go to the source data. That's exactly who things are in my utopian world you see. Come to think of it, it's been a long time since anyone called me back about prospective work.