cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Laz Member
Member

Calculating Last Year with SUMX and DateAdd

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 TypeStyle BarcodeDateSales PriceSales Units
S50533663730824/07/201889.162
S50533677195123/07/201852.011
S50533677195128/07/201844.581

 

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 Smiley Very Happy

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Laz Member
Member

Re: Calculating Last Year with SUMX and DateAdd

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 Smiley Happy

12 REPLIES 12
Super User
Super User

Re: Calculating Last Year with SUMX and DateAdd

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Stachu Super Contributor
Super Contributor

Re: Calculating Last Year with SUMX and DateAdd

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)
) 
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Laz Member
Member

Re: Calculating Last Year with SUMX and DateAdd

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

 

 

Laz Member
Member

Re: Calculating Last Year with SUMX and DateAdd

Hey @Stachu.

 

Thanks for reply Smiley Happy

 

No luck, but thanks for looking into it!

Super User
Super User

Re: Calculating Last Year with SUMX and DateAdd

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Laz Member
Member

Re: Calculating Last Year with SUMX and DateAdd

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
Super User
Super User

Re: Calculating Last Year with SUMX and DateAdd

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Laz Member
Member

Re: Calculating Last Year with SUMX and DateAdd

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

 

Laz Member
Member

Re: Calculating Last Year with SUMX and DateAdd

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,

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 275 members 2,953 guests
Please welcome our newest community members: