Solved: Calculating Last Year with SUMX and DateAdd - Page 2

Anonymous · ‎09-03-2018

Hi Everyone,

This is a follow up from a previous post i made, however i hit a wall once again and after much research, need some help:

https://community.powerbi.com/t5/Desktop/Help-with-SUMX-Calculating-Total-Sales/m-p/501415#M234006

I am importing the below data into PBI using Power Query:

Dimesnion Attributes:

1. Date

2. Barcode

3. Transaction Type

Measures:

1. Sales Price

2. Sales Units

Example of some records I import:

Ret Trans Type	Style Barcode	Date	Sales Price	Sales Units
S	505336637308	24/07/2018	89.16	2
S	505336771951	23/07/2018	52.01	1
S	505336771951	28/07/2018	44.58	1

Now that i have imported my data, what i must do is Multiply the Sales Price by the Sales Units: (Sales Price * Sales Units). The reason for this is to caclualte the Total Sales. However, i must use a SUMX as without the SUMX, the Grand Total would be incorrect from the multiplication of Price by Units.

So, below are the Dax Statements which have been derived and work great:

Sales Price (SUM) = SUM(SALES[Sales Price])

Sales Units (SUM) = SUM(SALES[Sales Units])

Total Sales Retail (SUMX) = 
SUMX(
 VALUES(SALES[[Dim Product]].[Style Barcode]].[Style Barcode]].[MEMBER_CAPTION]]])
 ,([1b. Sales Units (SUM)] * [1a. Sales Price (SUM)])
)

Now the challenge i am having is: With the above measures, i am deriving the LY (Last Year) equivalent measures using the two DAX Statements below:

LY Sales Price = CALCULATE(SUM(SALES[Sales Price]),
                    DATEADD('CALENDAR'[Dim Date.Date],-364,Day))

LY Sales Units = CALCULATE(SUM(SALES[Sales Units]),
                    DATEADD('CALENDAR'[Dim Date.Date],-364,Day))

Everyhting above works as expected with no data issue 😄

However, when i try create the LY (Last Year) Equivalent for Total Sales Retail (SUMX) using the below DAX i get NO values returned in the result set.

Total Sales Retail (SUMX) = 
SUMX(
    VALUES(SALES[[Dim Product]].[Style Barcode]].[Style Barcode]].[MEMBER_CAPTION]]])
    ,([LY Sales Units] * [LY Sales Price])
)

Any idea or help would be much appreciated.

Thank you