Lost Customers TY vs LY

Anything related to PowerPivot and DAX Formuale
bkrol
Posts: 12
Joined: Tue Apr 16, 2019 8:52 am

Lost Customers TY vs LY

Postby bkrol » Fri Jul 05, 2019 1:31 am

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 $ :=CALCULATE(
[Sales $],
FILTER(
ALL(Dates_by_Day),
Dates_by_Day[PeriodID]
>=MIN(Dates_by_Day[PeriodID])-365
&& Dates_by_Day[PeriodID]
<= MAX(Dates_by_Day[PeriodID])-365
)
)

The Pivot table has the following filter
Year = 2019 (this is so [Sales $] only shows sales for 2019

I also have the following slicers
Month (this allows me to select the months covered in the pivot table)
Past (this allows me to limit the [Sales LY $] on partial months, so it only shows through the current date and not the entire month for last year

I want the pivot table to only show the top 20 "Lost Customers" where the TY Sales $ = 0 and the LY Sales $ (for the sliced months) >0. It will have customer name, rep name, TY Sales, LY Sales, $ Diff, % Diff. It should sort from smallest $ Diff to greatest - since the figure is negative, the largest lost sales are at the top.

Not sure how to do it. When I try writing DAX, I keep getting error messages that I can't figure out.

Any suggestions would be greatly appreciated.

bkrol
Posts: 12
Joined: Tue Apr 16, 2019 8:52 am

Re: Lost Customers TY vs LY

Postby bkrol » Fri Jul 05, 2019 1:43 am

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.

bkrol
Posts: 12
Joined: Tue Apr 16, 2019 8:52 am

Re: Lost Customers TY vs LY

Postby bkrol » Fri Jul 05, 2019 2:09 am

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

MattAllington
Posts: 1053
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Lost Customers TY vs LY

Postby MattAllington » Fri Jul 05, 2019 7:44 am

Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

bkrol
Posts: 12
Joined: Tue Apr 16, 2019 8:52 am

Re: Lost Customers TY vs LY

Postby bkrol » Fri Jul 05, 2019 10:28 pm

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

MattAllington
Posts: 1053
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Lost Customers TY vs LY

Postby MattAllington » Sun Jul 07, 2019 9:27 am

Well it is a non trivial task requiring a non trivial solution. I have copied this code myself and it just worked.
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 5 guests