Forum Replies Created
-
Posts
-
Goshdarnit. I figured it out. It wasn’t anything to do with the measure. I added the Month name to the Columns below ACPeriod. It’s a 1:1 relationship, so I didn’t imagine it would matter.
Thank you so much for your help, Tom. I’m sorry to have put you to this trouble.
I don’t think it likes this part:
DateTable[ACYear] = VALUES ( DateTable[ACYear] )
It keeps telling me that it’s getting a table of results when only one value is expected.
I’m not sure that I can come up with an anonymized workbook, unfortunately. It’s just too involved. It would certainly make this easier.
How are VALUES() and HASONEVALUE() reapplying the filter? The results I’m getting are the same as before.
What really throws me is that ALL(DateTable[ACPeriod]) has no effect. Doing the same with [ACYear] works.
Edit: Okay, “works” may be an overstatement. It has an effect.
I’m afraid it wouldn’t. The quota is baked into each line of the data, but differs month to month. I have to do the SUMMARIZE(AVERAGE()) to get a single value per month before I can do any math with it.
Sorry for not replying sooner. I got swamped with new stuff at work and this slipped my mind. I do have a Calendar Table that I use. I’ve been going about multiple dates by creating multiple instances of it, but it sounds like USERELATIONSHIP() would be the better approach. I’ll read up on that and let you know if I’m still stuck.
Thank you!
Looks great. I’m going to have to wrap my head around this ADDCOLUMN() function, but it seems to be just what I needed. Thanks so much.
Thanks, Tom. I’ve downloaded the file. I’ll look it over first chance I get.
Actually, I’m not particularly worried about months. I’m looking for something like this:
2012 2013 2014 2015
Revenue from Last Year’s Customers 5 6 4 3
Revenue from Other Customers 3 2 2 4I need to be able to identify revenue as being from a customer who spent money with us the previous year or not.
I’ve looked over the resources you provided and I’m afraid I’m still stuck. There’s one formula that will calculate last year’s revenue, but I can’t seem to translate that into a calculated column that acts as a flag for last year revenue. Here’s my derived formula (minus the IF() function), which is pretty similar to what I was trying to begin with.
=COUNTROWS(CALCULATETABLE(Sales,ALL(DateTable),FILTER(ALL(DateTable[ACYear]),DateTable[ACYear]=EARLIER(DateTable[ACYear]))))
I keep getting a context error, though. Can you recommend anything?
Terrific! Thank you for all your help, Tom.
Here you go. Mostly just custom quarter end dates and everything that flows down from there. Nothing as simple as last day of the month, unfortunately.
I was briefly hopeful that today’s blog post would answer my question, but it was still dependent on the baked-in time intelligence functions.
Ahh, yes. This approach hadn’t occurred to me. It should work nicely if I’m not expecting to do many gymnastics with the Pivot Table.
I’m intrigued by this Sets business from your first reply. I think I’ll look into it and see if it’s closer to what I’d envisioned.
Thank you for your help, Tom!
Thanks, Tom! Excel 2013
Hopefully the code block is showing up better for you all than it is for me.
