Search found 12 matches

by bkrol
Fri Jul 05, 2019 10:28 pm
Forum: PowerPivot/DAX
Topic: Lost Customers TY vs LY
Replies: 5
Views: 317

Re: Lost Customers TY vs LY

Thanks,
That post specifically focuses on lost customers. The code looks a little intimidating, but if I can figure it out I will definitely have taken a step up in my understanding of DAX code.

Bill
by bkrol
Fri Jul 05, 2019 2:09 am
Forum: PowerPivot/DAX
Topic: Lost Customers TY vs LY
Replies: 5
Views: 317

Re: Lost Customers TY vs LY

I can put the measure [Lost Sales True False] in the values section of the pivot table, so it shows TRUE and FALSE so it shows Sales $ and Sales LY $, etc. But I don't know how to get it to only show the TRUE values
by bkrol
Fri Jul 05, 2019 1:43 am
Forum: PowerPivot/DAX
Topic: Lost Customers TY vs LY
Replies: 5
Views: 317

Re: Lost Customers TY vs LY

I tried to create a True / False measure and put it in the Filters section of the pivot table, but it wouldn't let me do that.
by bkrol
Fri Jul 05, 2019 1:31 am
Forum: PowerPivot/DAX
Topic: Lost Customers TY vs LY
Replies: 5
Views: 317

Lost Customers TY vs LY

I'm new to Power Pivot and got lost in the filters. I am trying to create a pivot table that shows "Lost Customers" Those are customers that had sales last year in the current period and sales this year = zero. The basic measures I'm using are Sales $ :=sum(Order_Hist[Extension]) Sales LY ...
by bkrol
Fri Jun 21, 2019 9:53 am
Forum: Power Query
Topic: Create a custom index column (for month or week)
Replies: 3
Views: 537

Re: Create a custom index column (for month or week)

I wanted to add that the best solution to the problem that I described was here by Ken Puls https://www.excelguru.ca/blog/2018/06/14/ranking-method-choices-in-power-query/ I used Ranking Method 4: Dense Rank Sort the YYYYMM column in descending order Go to Transform --> Group Group by the YYYYMM col...
by bkrol
Fri Jun 14, 2019 12:54 am
Forum: PowerPivot/DAX
Topic: Show only Top 10 AND Bottom 10 (hide everything in middle)
Replies: 1
Views: 248

Show only Top 10 AND Bottom 10 (hide everything in middle)

I have a pivot table that I have sorted by customer by variance from last year by smallest to largest. However, I have 500 customers. I really only want to see the 10 customers that had the largest negative variance and the 10 customers with the largest positive variance. I can use the values filter...
by bkrol
Sun Apr 28, 2019 10:29 am
Forum: Power Query
Topic: Create a custom index column (for month or week)
Replies: 3
Views: 537

Create a custom index column (for month or week)

I am creating a custom calendar table that covers several year. I want to create an index for things like fiscal month or week. If I was using excel and the fiscal month was in column A and the fiscal month index was in column B, then I would write a formula like =if(A2<>A1,B1+1,B1) and copy it down...
by bkrol
Thu Apr 25, 2019 12:20 am
Forum: Power Query
Topic: Calendar Table with Offsets and Fiscal Months
Replies: 3
Views: 557

Re: Calendar Table with Offsets and Fiscal Months

Bernard, Thanks for the file. I agree that creating it all in Power Query is the way to go. My challenge is coming up with the formula for our 4,4,5 calendar I'll try to create a formula to convert from calendar month to fiscal month. I guess that would work better than trying to do some type of loo...
by bkrol
Wed Apr 24, 2019 10:47 pm
Forum: Power Query
Topic: Calendar Table with Offsets and Fiscal Months
Replies: 3
Views: 557

Calendar Table with Offsets and Fiscal Months

I am trying to build a Master Calendar Table for my company using Power Query. My issue is that I can't get Power Query to figure out which fiscal month today belongs to. Some details on my situation and what I'm doing... We are on a calendar year, but use 4-4-5 fiscal months. I'm struggling with ti...
by bkrol
Wed Apr 24, 2019 10:18 pm
Forum: PowerPivot/DAX
Topic: By Month, Total, LY Total...
Replies: 2
Views: 304

Re: By Month, Total, LY Total...

Phil,

Thanks for the suggestion. I'm struggling to figure out all the nuances with time intelligence. I don't think I was using SAMEPERIODLASTYEAR. That sounds like the solution.

Bill

Go to advanced search