Calculating Last Year with SUMX and DateAdd

[Anonymous]

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

[Stachu]

have you tried adding the time filter on top of the original SUMX? something like this

Total Sales Retail LY (SUMX) = 
CALCULATE(
 [Total Sales Retail (SUMX)],
 DATEADD('CALENDAR'[Dim Date.Date],-364,Day)
) 

[Anonymous]

Hey @Stachu.

Thanks for reply 🙂

No luck, but thanks for looking into it!

[Greg_Deckler]

I would do something like this:

Measure 8 = 
VAR __tmpTable = SUMMARIZE(Table15,Table15[Ret Trans Type],Table15[Style Barcode],"__SalePrice",SUM(Table15[Sales Price]),"__Sales Units",SUM(Table15[Sales Units]))
VAR __tmpTable1 = ADDCOLUMNS(__tmpTable,"__Total",[__SalePrice]*[__Sales Units])
RETURN SUMX(__tmpTable1,[__Total])

You should then be able to use this as the basis for calculating LY by using my Time Intelligence The Hard Way Quick Measure here:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[Anonymous]

Hi @Greg_Deckler,

I tried create a new measure from the Dax you shared!

However, i am still getting the same result of the column returning no values.

When i am trying to return the Total Sales for TY (This Year) by multiplying Sales Price by Sales Units, the three below DAX Statements 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)])
)  

What i am trying to achieve is to create the three above, but for LY (Last Year):

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))

The challenge i am having is when i try creating the Equivalent for “Total Sales Retail (SUMX)” but for LY (Last Year) from the below DAX Statement:

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

Using your DAX which you kindly shared:

Measure 8 (TY) = 
VAR __tmpTable = SUMMARIZE(SALES,SALES[Trans Type],SALES[[Dim Product]].[Style Barcode]].[Style Barcode]].[MEMBER_CAPTION]]],"__SalePrice",[Sales Price],"__Sales Units",[Sales Units])
VAR __tmpTable1 = ADDCOLUMNS(__tmpTable,"__Total",[__SalePrice]*[__Sales Units])
RETURN SUMX(__tmpTable1,[__Total])

 I am returning the same result as the measure I already have “Total Sales Retail (SUMX)” – which is correct.

However, when I apply the same to derive the LY (Last Year) equivalent:

Measure 8 = 
VAR __tmpTable = SUMMARIZE(SALES,SALES[Trans Type],SALES[[Dim Product]].[Style Barcode]].[Style Barcode]].[MEMBER_CAPTION]]],"__SalePrice",[LY Sales Price],"__Sales Units",[LY Sales Units])
VAR __tmpTable1 = ADDCOLUMNS(__tmpTable,"__Total",[__SalePrice]*[__Sales Units])
RETURN SUMX(__tmpTable1,[__Total])

 I am returning the same result as the measure I already have “Total LY Sales Retail (SUMX)” – which is incorrect.

Sorry if i did not completely grasp the DAX you shared!

Any other input would be much appreciated

Thanks,

[Greg_Deckler]

I would add a Year column to your table:

Year = YEAR([Date])

Then in your LY calculation, create this variable:

VAR __year = MAX([Year])

And then in your SUMX just FILTER yoru table to [Year]=__year - 1

[Anonymous · Accepted solution]

Hi @Greg_Deckler,

I had managed to achieve the outcome i was looking for in Power BI through going back to the Drawing Board.

All my issues were resolved once i imported at a Transaction ID level, instead of my lowest level being Barcode, which is essentially Product ID.

I can explain in more detail, but that would be pointless now. With that said, thank you for all your input 🙂

[Maikeru]

Dear @Anonymous
I happen to be facing a similar issue as you (SUMX formula on a time intelligence function), and wouldl be interested if you could share the details of the solution.

Thanks in advance!

Michael

[Anonymous]

Hi @Greg_Deckler

I tried create a new measure from the Dax you sahred!

However, i am still getting the same result of the column returning no values.

When i am trying to return the Total Sales for TY (This Year) by multiplying Sales Price by Sales Units, the three below DAX Statements 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)])
)  

What i am trying to achieve is to create the three above, but for LY (Last Year):

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))

The challenge i am having is when i try create the Equivalent for “Total Sales Retail (SUMX)” but for LY (Last Year) from the below DAX Statement:

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

I am getting back no data, which i am assuming is because i am using "LY Sales Price" and "LY Sales Units" which both include DATEADD.

Using your DAX which you kindly shared:

Measure 8 (TY) = 
VAR __tmpTable = SUMMARIZE(Table15,Table15[Ret Trans Type],Table15[Style Barcode],"__SalePrice",SUM(Table15[Sales Price]),"__Sales Units",SUM(Table15[Sales Units]))
VAR __tmpTable1 = ADDCOLUMNS(__tmpTable,"__Total",[__SalePrice]*[__Sales Units])
RETURN SUMX(__tmpTable1,[__Total])

Is giving me the exactly same result as the measure i am currently using "Total Sales Retail (SUMX)" - which is correct.

When i modify your DAX to incorporate the LY Measures as:

Measure 8 (LY) = 
VAR __tmpTable = SUMMARIZE(SALES,SALES[Trans Type],SALES[[Dim Product]].[Style Barcode]].[Style Barcode]].[MEMBER_CAPTION]]],"__SalePrice",[LY Sales Price],"__Sales Units",[LY Sales Units])
VAR __tmpTable1 = ADDCOLUMNS(__tmpTable,"__Total",[__SalePrice]*[__Sales Units])
RETURN SUMX(__tmpTable1,[__Total])

I am getting the same result as i am with my LY SUMX measure "Total LY Sales Retail (SUMX)".

If i understood correctly, using your piece of DAX as a replacement for my "Total LY Sales Retail (SUMX)", should put me on the path of finding a solution. However, it seems that as i am using "LY Sales Price" and "LY Sales Units" which both include DATEADD, the result is still retuning NOTHING.

Thanks for helping

[Anonymous]

Hi @Greg_Deckler

Thank you!

I am away from screen but can’t wait to try this.

This looks like something out of my depth, hence even more excited to try it out!
I will be sure to feedback with the outcome.

Thanks again,

Laz

[Greg_Deckler]

Will be interested in the result. BTW, if you created a new Table, you could use the same formula but RETURN __tmpTable1 and then you would have a table that would resolve your issue of having purchase lines separated as 2 rows. Might work for your situation and make subsequent calculations trivial.

[Greg_Deckler]

Why not just create a calculated column that multiplies your two columns together?

[Anonymous]

Hi @Greg_Deckler

Firstly, thanks for replying and trying to help!

So, i have explained why using a Caclulated Column that multiplies the two columns would not work in a previous post i made and therefore the SUMX was used as the solution:

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

But to give more details, if you look as below table, this is how the data is in Power Query. If we multiply the below two records, we will return the incorrect value when deriving Total Sales (Sales Price * Sales Units), as The same barcode is seperated in two records so:

52.01 * 1 = 52.01

44.58 * 1 = 44.58

But what i need in the front end would be to have:

96.59 * 2 = 193.18

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

Hopefully this explains why i cant use calculated columns

Thanks