cancel
Showing results for
Search instead for
Did you mean:
Highlighted
Laz 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 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

1 ACCEPTED SOLUTION

Accepted Solutions
Laz Member

## Re: Calculating Last Year with SUMX and DateAdd

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 12 REPLIES 12 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

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

## Re: Calculating Last Year with SUMX and DateAdd

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

## Re: Calculating Last Year with SUMX and DateAdd

Hey @Stachu.

Thanks for reply No luck, but thanks for looking into it! 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

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

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

## Re: Calculating Last Year with SUMX and DateAdd

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

## Re: Calculating Last Year with SUMX and DateAdd

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
Top Ideas Top Kudoed Authors
Users Online
Currently online: 379 members 3,589 guests
Recent signins:
• jrscaletta • raman_evs • Kopite_sid • asmith049 • dmotter • ChrisPollock • barb2ko2 Please welcome our newest community members:
• Archonik • mposata • VNB_VipulSingh • ntc4 • hdzhendov • artiv18 • MSpreafico 