Power Pivot/DAX: Problem with Like for Like Sales Figure

[Jubjab]

I am trying to build a DAX measure in a Power Pivot to show the Like for Like sales figure (LFL). LFL is simply a measure to show the sales if the customer also had sales during the same period last year.

I can not get any figures at all for this, and this is due to the previous year not working for some reason.

The formula I am trying to use is

=CALCULATE([Total Sales];FILTER(Data;[Total Sales]>0 && [Prev.year sales]>0))

This formula works but gives no data for any selection.

Just to test, I made formula just to show previous year's sales. Also this formula gives no values for any customers.

=CALCULATE([Prev.year Sales];FILTER(Data;[Prev.year Sales]<>0))

The formula [Prev.year Sales] looks like this, and does work as intended:

=CALCULATE([Total Sales];SAMEPERIODLASTYEAR('Calendar'[Date]))

Any ideas what is wrong? Thanks!

 

[Misca]

If you're interested in the customers that had sales last year as well as this year try filtering the customers table instead of your fact table. Something like

[COLOR=#333333][FONT=Consolas]Sales Change :=[/FONT][/COLOR]
[COLOR=#0070FF][FONT=Consolas]CALCULATE[/FONT][/COLOR][COLOR=#969696][FONT=Consolas] ([/FONT][/COLOR]
[COLOR=#333333][FONT=Consolas]    [/FONT][/COLOR][COLOR=#333333][FONT=Consolas][Total Sales] - [Sales LY];[/FONT][/COLOR]
[COLOR=#333333][FONT=Consolas]    [/FONT][/COLOR][COLOR=#0070FF][FONT=Consolas]FILTER[/FONT][/COLOR][COLOR=#969696][FONT=Consolas] ([/FONT][/COLOR][COLOR=#333333][FONT=Consolas] [/FONT][/COLOR][COLOR=#0070FF][FONT=Consolas]VALUES[/FONT][/COLOR][COLOR=#969696][FONT=Consolas] ([/FONT][/COLOR][COLOR=#333333][FONT=Consolas] Customers[Name] [/FONT][/COLOR][COLOR=#969696][FONT=Consolas])[/FONT][/COLOR][COLOR=#333333][FONT=Consolas]; [Total Sales] > [/FONT][/COLOR][COLOR=#EE7F18][FONT=Consolas]0[/FONT][/COLOR][COLOR=#333333][FONT=Consolas] && [Sales LY] > [/FONT][/COLOR][COLOR=#EE7F18][FONT=Consolas]0[/FONT][/COLOR][COLOR=#333333][FONT=Consolas] [/FONT][/COLOR][COLOR=#969696][FONT=Consolas])[/FONT][/COLOR]
[COLOR=#969696][FONT=Consolas])[/FONT][/COLOR]

should work.

 

[Jubjab]

I'm not really following you here, sorry. To clarify.

I simply want my new measure to show the customer's sales, if it also has sales during the same period last year.

So

Customer Total Sales Prev.year Sales LikeForLike
A____________100_________ 50 _________100_____
B____________200_____________________________
C____________150_________225__________150____
D____________175 ________170 __________175____
E________________________500_________________


If I use the DAX formula

=CALCULATE([Total Sales];FILTER(Data;[Total Sales]>0))

I get the same values as the Total Sales (obviously). But if I make the same formula for last year, i.e.

=CALCULATE([Prev.year Sales];FILTER(Data;[Prev.year Sales]>0))

I get no values at all. Why is that?

 

[Jubjab]

To further clarify, all needed data is residing in the Data table. It's a list of all sales, including the customer name. So for the purposes of this measure, I only need the data table and the calendar. I don't have a separate customer table.

 

[Misca]

Try:
LFL :=
CALCULATE (
[Total Sales];
FILTER ( VALUES ( Sales[Customer] ); [Total Sales] > 0 && [Sales LY] > 0 )
)

 

[Jubjab]

Great! This works Kiitos! You are my new hero.