This topic contains 12 replies, has 3 voices, and was last updated by 
-
Posts
-
I feel like this should be a simple question, but everything I’ve tried fails. I need to be able to split each year’s revenue between customers who spent money with us last year and customers who hadn’t. Part of my trouble is that I have to use a custom Date Table, owing to the structure of our accounting year. Otherwise, I could clearly just use the time/date intelligence features. I’ve been trying to create a calculated column in the sales table that unfilters the date table, then refers back to the sales table using EARLIER(), but it’s just not working. I even gave up at one point on doing it in a single column and just hardcoded the years into separate calculated columns, but I kept getting inexplicable circular dependency errors. Any suggestions?
Sounds like it should not be hard.
How is your calendar table customized? Can you include your custom calendar in a workbook and attach the workbook to your next reply?
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.
Interesting calendar table. Reminds of the accounting calendar when I worked for a manufacturing company.
Actually looks pretty good. DayNum, WeekNum, MonthNum, QuarterNum, YearNum seem to increment steadily upward from the earliest date to the last.
A web site that has DAX formulas that show custom calendar equivalents of the time intelligence functions can be found at:
http://www.daxpatterns.com/time-patterns/
Site also has an excellent description of how the time intelligence functions work.
Hello volfied,
Just following up.
Also came across a circular reference message in Power Pivt when creating a calculated column for the dates table to sort the short month names by accounting year order.
I switched my focus from Power Pivot to the copy of the dates table in Excel and added a column MonthSort with a VLOOKUP formula to incorporate the short month name sorting logic. The formula for MonthSort looked like this:
MonthSort = VLOOKUP( [@Month], $AB$2:$AC$13, 2, FALSE)
And the range of $AB$2:$AC$13 looked like
Oct 1
Nov 2
Dec 3
Jan 4
Feb 5
Mar 6
Apr 7
May 8
Jun 9
Jul 10
Aug 11
Sep 12Once the dates table was refreshed inside of Power Pivot, I choose to hide the MonthSort column from client tools and used it as a “Sort by” column for Month so the display sequence of Month in a pivot would be by fiscal year sequence, not alpha. If you are interested in implementing this column, I would recommend pushing the logic back into your SQL query.
Hope that helped. A couple other places to look for insights on custom calendar equivalents of time intelligence functions would be Rob Collie’s book, “DAX Formulas for Power Pivot” and Kasper De Jonge’s book, “Dashboarding and Reporting with Power Pivot and Excel”.
Another thought: you may find a solution to the current request for splitting “each year’s revenue between customers who spent money with us last year and customers who hadn’t” in references to new, returning, lost and recovered customers on the powerpivotpro and other websites.
Terrific! Thank you for all your help, Tom.
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?
I can recommend something, but it will be a measure (a calculated field) and not a calculated column.
Let me make sure I understand what you want. I believe you want formulas for at least two sets of numbers: current fiscal year’s revenues broken out into fiscal months, in addition to a formula that produce the same for the prior year.
I also think you are looking for a way for separating customers who made purchases during a given fiscal year from those who did not.
Let me know if I have that right.
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.
Volfied,
See attached workbook.
Feel free to ask questions, one of the DAX formulas is advanced.
Unable to upload file, here is dropbox link to download:
Please let me know after you download from dropbox and I will remove link.
Thanks, Tom. I’ve downloaded the file. I’ll look it over first chance I get.
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.
Hi Tom, Volfied,
This is an interesting problem with an even so interesting solution it seems.
I hope you don’t mind that I downloaded the solution workbook to take a look at tomorrow at work.
If so, please let me know and I will delete it.
Thanks!
The forum ‘Power Pivot’ is closed to new topics and replies.