Home Forums Power Pivot Revenue from Last Year's Customers

This topic contains 12 replies, has 3 voices, and was last updated by  kirvis 5 years, 2 months ago.

Viewing 13 posts - 1 through 13 (of 13 total)
  • Author
    Posts
  • #1318

    volfied
    Participant
    • Started: 8
    • Replies: 14
    • Total: 22

    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?

    #1329

    tomallan
    Keymaster
    • Started: 0
    • Replies: 417
    • Total: 417

    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?

    #1330

    volfied
    Participant
    • Started: 8
    • Replies: 14
    • Total: 22

    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.

    Attachments:
    You must be logged in to view attached files.
    #1332

    tomallan
    Keymaster
    • Started: 0
    • Replies: 417
    • Total: 417

    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.

    #1334

    tomallan
    Keymaster
    • Started: 0
    • Replies: 417
    • Total: 417

    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 12

    Once 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.

    #1336

    volfied
    Participant
    • Started: 8
    • Replies: 14
    • Total: 22

    Terrific! Thank you for all your help, Tom.

    #1339

    volfied
    Participant
    • Started: 8
    • Replies: 14
    • Total: 22

    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?

    #1340

    tomallan
    Keymaster
    • Started: 0
    • Replies: 417
    • Total: 417

    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.

    #1344

    volfied
    Participant
    • Started: 8
    • Replies: 14
    • Total: 22

    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                   4

    I need to be able to identify revenue as being from a customer who spent money with us the previous year or not.

    #1348

    tomallan
    Keymaster
    • Started: 0
    • Replies: 417
    • Total: 417

    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:

    https://www.dropbox.com/s/kuima943pgtb7dg/Revenue%20from%20Last%20Year%27s%20Customers%20Rev%2001.xlsx?dl=0

    Please let me know after you download from dropbox and I will remove link.

    #1358

    volfied
    Participant
    • Started: 8
    • Replies: 14
    • Total: 22

    Thanks, Tom. I’ve downloaded the file. I’ll look it over first chance I get.

    #1359

    volfied
    Participant
    • Started: 8
    • Replies: 14
    • Total: 22

    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.

    #1367

    kirvis
    Participant
    • Started: 0
    • Replies: 4
    • Total: 4

    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!

Viewing 13 posts - 1 through 13 (of 13 total)

The forum ‘Power Pivot’ is closed to new topics and replies.