[johnkhydeParticipant Started: 0Replies: 16Total: 16]

I though that I read that Rob Collie advised against having your calendar table go beyond the last date for which you have data?

 

Distinct count is not working (or I am doing it wrong) because I need to only count certain customers. For example, where a customer joined in 2011 (sub category in rows) and still made purchases in 2014 (years as columns). Customers made purchases in 2014 but joined in various years. I am trying to subtotal the # of customers based on the year they joined (rows) and the years they made purchases.

# of Customers Example:

calendar Yr     2011     2012    2013    2014    2015

Yr joined

2011                  100        50        25         15        7

2012                   125       75        50         25      15

2013                   150      100      75         50      30

 

Total Cust        375       175      150        90    52

[johnkhydeParticipant Started: 0Replies: 16Total: 16]

Tom,

I started this post because I could not figure out how to count the customers that were buying things on their 1st, 2nd, 3rd… anniversary years. As I was typing the formulas in this post I figured it out! But I get an error message in the out years because the time period does not exist. For example, a customer that started in 2015, has not made purchases 3 years out, 4 years out, etc. Even if they started Dec 2015, they have not made purchases thru Dec 2016. How do I trap this error, or better yet, count customers to date in those out years?

My formulas are as follows:

[Customer First Order Date]: FIRSTNONBLANK(ALL(Calendar[Date]),[Sales])

[Customer Yr 1 Anniversary]: [Customer First Order Date]+365

[Customer Yr 2 Anniversary]: [Customer Yr 1 Anniversary]+365

[# of Customers Mths 13-24]: =IFERROR(CALCULATE([# of Customers],DATESBETWEEN(Calendar[Date],[Customer Yr 1 Anniversary],[Customer Yr 2 Anniversary])),
0)

Edit: In addition, I think I need to make this a countx/countax bc some of my totals are off? suggestions?

[johnkhydeParticipant Started: 0Replies: 16Total: 16]

Thanks Tom. I actually read in one of your other posts about Rob’s online training so I bought it earlier this week and have been watching them. Great videos, thanks for the recommendation!

I also bought Rob’s book Dax Formulas for Powerpivot the other week and have been reading that too. All great stuff!

I understand the power of portability and did not know I could hide measures. I will rework the correct way. [Edit: I do not see a hide measure option when I right click on a measure?]

My next challenge is the customer counts (retention) of original customers over time. My first few attempts have not been successful, but I will keep trying!

 

Have a great weekend Tom.

 

John

[johnkhydeParticipant Started: 0Replies: 16Total: 16]

Tom, This is what I came up prior to seeing your last post. The reason my formula is so long is that I did not want another measure that is exactly the same as the original except that this new formula includes a sumx’s, bc the prior formula is useless bc the total is wrong. So your solution is the same in that it forces me to have more measures where the existing measures are useless bc they don’t sum.

Also, I understand that [Sales Mths 1-12]=CALCULATE([Sales],DATESINPERIOD(Calendar[Date],
[Customer First Order Date],
12,month))

is portable into a new formula, but I am starting to have too many measures!

So I revised the original formula to be:

[Sales Mths 1-12 SumX]=If(HASONEVALUE(Customers[Since_DT Mth]),
CALCULATE([Sales],DATESINPERIOD(Calendar[Date],
[Customer First Order Date],
12,month)
),
SUMX(VALUES(Customers[Since_DT Mth]),
CALCULATE([Sales],DATESINPERIOD(Calendar[Date],
[Customer First Order Date],
12,month)
)
) )

 

And it works, but it is long. Is this the most efficient way?

[johnkhydeParticipant Started: 0Replies: 16Total: 16]

Thanks Tom. Those were good ideas and I made those changes.

Now my totals don’t add up? I assume this is a sumx solution, but I am not sure who to in conjunction with my other formulas.

Maybe this is some combo hasonevalue(values(sumx…?

 

John

[johnkhydeParticipant Started: 0Replies: 16Total: 16]

Not sure this is the best method, but it is working for subsequent periods!

[sales mths 13-24]=CALCULATE([Sales],DATESINPERIOD(Calendar[Date],
[Customer First Order Date]+365,
12,month)
)

[johnkhydeParticipant Started: 0Replies: 16Total: 16]

wa-hoo! figured out part of it! This gets first 12 months in one column! Now I need to get months 12-24, 24-36…

[Sales Mths 1-12]=CALCULATE([Sales],DATESINPERIOD(Calendar[Date],
[Customer First Order Date],
12,month))

[Customer First Order Date]=FIRSTNONBLANK(ALL(Calendar[Date]),[Sales])

[johnkhydeParticipant Started: 0Replies: 16Total: 16]

Thanks for your response Tom.

I have reviewed your design and I have reviewed my diagram view and I believe that I am already doing as you suggested. Perhaps the naming conventions are causing some confusion.

Power Pivot Database naming conventions

Sales Table = Data2 table

Transaction date column = DLI_ACT_DT

Transaction Amount = DLI_BAL

First transaction date = Since_DT

**************************************************

Target Report Definitions

Y(#)$ = sum first 12 months of sales starting on the mth/yr indicated in the row context

Cust = # of customers that made first purchase on the mth/yr indicated in the row context

Avg $ = Y(#)$ / Cust

1st mo = Total sales in first month / total customers that made 1st purchase that month.

Ret = # of customers that returned / the number of customers in the first year, not the prior year.

I am hesitant to download Power Query because it requires a MS Explorer (MSE) version upgrade and I don’t believe that some of our accounting systems will work with the newer version of MSE, so I need to verify with our IT dept first.

Hopefully this clarifies some things.

Thanks again for your help.

John

[johnkhydeParticipant Started: 0Replies: 16Total: 16]

Ok, I’m getting better at this 🙂

I went back to the smaller file from 2 days ago. No idea why it doubled in size yesterday, but I suspect it had to do with me refreshing data, maybe pulling in many, many blank rows by refreshing incorrectly.

Anyway, I have attached a cleaned up zip file. In the pivot table, I have highlighted the cells green that I want summarized like the 2nd summary file I attached in my 3rd post on January 22, 2016 at 6:02 pm.

I look forward to any advice you can provide Tom.

Thanks

John

Attachments:

You must be logged in to view attached files.

[johnkhydeParticipant Started: 0Replies: 16Total: 16]

I am going back to my smaller 8MB file from 2 days ago and am going to try and add the new measures I created yesterday and be more careful…

 

I identified the information I wanted in a summarized table, but I need to pull it into individual columns.

[johnkhydeParticipant Started: 0Replies: 16Total: 16]

Hi Tom, I have answered your questions below in ALL CAPS. Right now I am having difficulty with my file size. I have tried to put more reference items in the lookup tables and remove it from the data table to shrink the file size, but it has doubled the file size, so I can’t even seem to get it small enough to load into this forum post.

 

“I can help you with your calendar table (most models only need one), but I get a feeling you have a chunk of frustration with progress so far.” I AM ENJOYING LEARNING, BUT AM A LITTLE FRUSTRATED.

 

“The original workbook that I downloaded was created in Excel 2010, do you work with a 32 bit or 64 bit version of Excel?” 32 BIT, BUT I HAVE REQUESTED MORE MEMORY AND POSSIBLY 64 BIT FROM IT DEPT. HOPEFULLY I GET IT.

“When you describe you master file as huge, do you mean it is so large Excel freezes while loading data or is the master file so large that performance is poor or something else?” THE FILE SIZE HAS INCREASED FROM 8MB TO 16 MB WITHOUT ANY NEW DATA. SOMETIMES PERFORMANCE IS SLOW.

“Is your data source for Power Pivot a company database (like SQL Server or Oracle or Access) or something else? If something else, is it other workbooks or text files, etc.?” EXCEL FILES, BUT ULTIMATELY I WANT DIRECT ACCESS.

“How many rows are in your master file?’ 136,000 ROWS IN THE MASTER FILE

“In terms of megabytes, how big is your master file?” THE FILE SIZE HAS INCREASED FROM 8MB TO 16 MB WITHOUT ANY NEW DATA.

“Other than your master table, what other tables does your power pivot model have, and how many rows?” I HAVE A CALENDAR TABLE WITH 5K ROWS, CUSTOMER TABLE WITH 18K ROWS, SOME OTHER TABLE THAT ARE MINUSCULE.

“What lookup (dimension) tables have you cut out so far?” I THINK MY PROBLEM STARTED WHEN I CUT OUT CUSTOMER RELATED INFO FROM MY DATA TABLE AND FILTERED OUT DUPLICATES AND PUT INTO THE CUSTOMER TABLE.

[johnkhydeParticipant Started: 0Replies: 16Total: 16]

Hi Tom, that formula did not work. My master file is huge so in order to get the file small enough to upload I have to cut out a lot of data so I cut out all of the look up tables in addition to a lot of data. Maybe I will cut out more years of data and leave lookup tables? I’ll do that after my meeting is over

what I think I need to do is somehow create new temporary calendar tables that originate the month-year of initial purchase and then sumx? those results for next 12, 24, 36, 48 months… But how?

John

[johnkhydeParticipant Started: 0Replies: 16Total: 16]

So I have some things working, but not everything. First Month Sales works, but I can’t get any future months related to the customers in the first group. Every month only shows those customers that started that month.

First Month Sales=CALCULATE([Sales],FILTER(Customers,[Was Customer First Order in Period]=1))

Was Customer First Order In Period:=IF(FIRSTDATE(Calendar[Date])<=[Customer First Order Date],IF(LASTDATE(Calendar[Date])>=[Customer First Order Date],1,0),0)

Customer First Order Date=FIRSTNONBLANK(ALL(Calendar[Date]),[Sales])

New Customer via First Order Date=COUNTROWS(FILTER(Customers,[Was Customer First Order in Period]=1))

New Customer via first Order Date: =COUNTROWS(FILTER(Customers,[Was Customer First Order in Period]=1))

[johnkhydeParticipant Started: 0Replies: 16Total: 16]

Bump

[johnkhydeParticipant Started: 0Replies: 16Total: 16]

Hi Tom, Please see attached file. My boss put this together with a lot of manual effort on his part, to help me understand what he wants. I am sure that powerpivot can do this and more. He wants to be able to view this same info for other years, and going forward.

You will notice that Jan 2011 amount is $101,070 and ties to my pivot table (blue shaded cells). Feb 2011 are the next 12 months of sales of those customers that started in Feb 2011. To the right, you will see the sales of those customers from months 13-24 and so on.

Does this help explain what I am trying to do?

Thanks

John

Attachments:

You must be logged in to view attached files.

[Author]

Posts