Row level measure syntax: (Current year data) minus (SAMEPERIODLASTYEAR data) * Quantity

[Mark_H]

Hi,

I'm new to DAX... I am trying to create a measure that calculates (i.e. calculates row by row, then aggregates):

( (Current year sales / Current year quantity) - (Prior year sales / Prior year quantity) ) * (Current year quantity) = $

= ((Ave sell price) - (Ave sell price prior year)) * Current Quantity = $

= (ASP - ASP_LY) * QTY = $

The calc needs to occur at a row level first to get the full row $ result, then aggregate these $'s.

My attempts have beeen successful when tested as individual measures, i.e ASP and ASP_LY measures in the below picture show a result (which in their cases is a $/qty unit so as their own measure they give a meaningless agreggated result). For my desired measure ((ASP - ASP_LY) * QTY = $), I need these to all be calculated first at a row level, then aggreageted to return a $ amount. If not done at a row level the result is incorrect.

When I attempted what I thought as OK syntax to throw all within a SUMX, I do not get the result I want. I'm guessing that the ASP_LY which uses SAMEPERIODLASTYEAR term, inside the SUMX is filtering data out??

The end result is exactly the same as ASP measure result on its own, so it appears that the ASP_LY and QTY are not being picked up and calculated, or returning as 0.

I am using transaction level sales data for a table, so was hoping to create a measure that brought this together for my data analysis page. The intent for SAMEPERIODLAST YEAR is I have page filters, including a date one, to interogate different time periods.

Code is below, hope someone can help please...!

Pri-Vol Price =

sumx(

    //ASP

    AllSalesData,

    (divide(AllSalesData[value],AllSalesData[qty])-

    //ASP LY

    CALCULATE(

    sumx(AllSalesData,divide(AllSalesData[value],AllSalesData[qty])),

    SAMEPERIODLASTYEAR(

        AllSalesData[invoice_date].[Date]

    ))*

    //QTY

    AllSalesData[qty]

)

)

Thanks in advance, Mark

[Mark_H]

Thanks for the quick return Ashish.

Hopefully the below makes sense, remebering I was trying to do in within a measure.

1) Transaction level data:

order_no	invoice_date	stock_code	qty	value
10000	1/01/2021	Product A	2	20.2
10001	15/01/2021	Product D	4	51.2
10002	1/02/2021	Product B	7	93.8
10003	15/02/2021	Product C	5	78.5
10004	1/03/2021	Product B	6	58.8
10005	15/03/2021	Product E	1	10.8
10006	1/04/2021	Product E	3	31.2
10007	15/04/2021	Product C	8	100.8
10008	1/05/2021	Product B	9	135
10009	15/05/2021	Product A	7	58.1
10010	1/06/2021	Product C	7	74.9
10011	15/06/2021	Product D	1	16.4
10012	1/07/2021	Product A	5	63
10013	15/07/2021	Product A	9	90.9
10014	1/08/2021	Product E	6	76.8
10015	15/08/2021	Product E	4	53.6
10016	1/09/2021	Product D	2	31.4
10017	15/09/2021	Product A	3	29.4
10018	1/10/2021	Product C	7	75.6
10019	15/10/2021	Product B	8	83.2
10020	1/11/2021	Product D	7	88.2
10021	15/11/2021	Product B	1	15
10022	1/12/2021	Product E	5	41.5
10023	15/12/2021	Product E	5	53.5
10024	1/01/2022	Product B	3	33.3
10025	15/01/2022	Product C	5	69
10026	1/02/2022	Product E	8	115.2
10027	15/02/2022	Product D	6	100.2
10028	1/03/2022	Product C	7	75.6
10029	15/03/2022	Product E	2	23.6
10030	1/04/2022	Product E	4	45.6
10031	15/04/2022	Product C	9	122.4
10032	1/05/2022	Product C	10	160
10033	15/05/2022	Product A	8	74.4
10034	1/06/2022	Product A	8	93.6
10035	15/06/2022	Product A	2	34.8
10036	1/07/2022	Product C	6	81.6
10037	15/07/2022	Product C	10	111
10038	1/08/2022	Product C	7	96.6
10039	15/08/2022	Product C	5	72
10040	1/09/2022	Product B	3	50.1
10041	15/09/2022	Product D	4	43.2
10042	1/10/2022	Product D	8	94.4
10043	15/10/2022	Product E	9	102.6
10044	1/11/2022	Product B	8	108.8
10045	15/11/2022	Product A	2	32
10046	1/12/2022	Product B	6	49.8
10047	15/12/2022	Product E	6	64.2

2. Correct calculation result (row by row calulation then aggregation):

ASP = value (2022) / qty (2022)

ASP_LY = value (2021) / qty (2021)

Price = (ASP - ASP_LY) * qty (2022)

	qty	qty	value	value
	2021	2022	2021	2022		ASP	ASP_LY	Price
Product A	26	20	261.6	234.8		11.74	10.06	33.57
Product B	31	20	385.8	242		12.10	12.45	-6.90
Product C	27	59	329.8	788.2		13.36	12.21	67.53
Product D	14	18	187.2	237.8		13.21	13.37	-2.89
Product E	24	29	267.4	351.2		12.11	11.14	28.09
								119.40

3. Incorrect calculation result (aggregation then calculate formula)

ASP = value (2022) / qty (2022)

ASP_LY = value (2021) / qty (2021)

Price = (ASP - ASP_LY) * qty (2022)

	qty	qty	value	value
	2021	2022	2021	2022		ASP	ASP_LY	Price
Product A	26	20	261.6	234.8		11.74	10.06	33.57
Product B	31	20	385.8	242		12.10	12.45	-6.90
Product C	27	59	329.8	788.2		13.36	12.21	67.53
Product D	14	18	187.2	237.8		13.21	13.37	-2.89
Product E	24	29	267.4	351.2		12.11	11.14	28.09
		146				62.52	59.23	479.78

[Thennarasu_R]

1.ASP = VAR A=CALCULATE([VALUEMEASURE],(YEAR COLUMN)="2022")
             VAR B=CALCULATE([QTYMEASURE],(YEAR COLUMN)="2022")
RETURN
     DIVIDE(A/B)

2.mEASURE =VAR A=CALCULATE([VALUEMEASURE],(YEAR COLUMN)="2021")
             VAR B=CALCULATE([QTYMEASURE],(YEAR COLUMN)="2021")
RETURN
     DIVIDE(A/B)

3.Price = VAR a=SUMX(ASP - ASP_LY) 
               VAR B=CALCULATE([QTYMEASURE],(YEAR COLUMN)="2022")
            RETURN
                      SUMX(a*b)
Try check this 

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Mark_H]

Thanks Ashish, we are getting closer...

Maybe because I gave you fictional data and not described my desired outcome you've created a literal version... I wish to be able to summarise the 'price' calculated by whatever view I choose. So if I take your response and change to view by month instead of product, I get an error in the ASP_LY and therefore price too.

Sorry if I was not clear, I am trying to make this measure 'smart' and 'dynamic' so I can interogate data in whatever way I wish. Hence I though that using the SAMEPERIODLASTYEAR in the SUMX would somehow dynamically adjust to my desired view at the time...

Does that make more sense?

[Ashish_Mathur]

You are welcome.  You may download my revised PBI file from here.

Hope this helps.

[Ashish_Mathur · Accepted solution]

Hi,

Share some data to work with (in a format that can be pasted in an MS Excel file) and show the expected result.

[Mark_H]

I have a few more if you can help @Ashish_Mathur or @Thennarasu_R 

Re: my previous solultion, I also want to work out:

Lost Volume = if(current year volume=0 & prior year volume>0), prior year sales)

My code works at a row level, but of course fails at the total:

Lost Volume = if([quantity]=0 && [quantity_LY]>0,sumx(values(AllSalesData[invoice_date].[Date]),-[revenue_LY]),0)

When I try to wrap this in a sumx(values(... to get the total and rows to work, it does not work

Hoping you can help there too?

Mark

[Ashish_Mathur]

Try this measure

Lost volume = SUMX(FILTER(SUMMARIZE(CALCULATETABLE(VALUES(Data[order_no]),DATESBETWEEN('Calendar'[Date],edate(min('Calendar'[Date]),-12),min('Calendar'[Date])-1)),[order_no],"ABCD",[Quantity],"EFGH",[Quantity in PY],"IJKL",[Revenue in PY]),[ABCD]=0&&[EFGH]>0),[IJKL])

[Mark_H]

Amazing, works, really appreciate it!

A few bugs to work out on my side, but the syntax works very well!

Mark

[Ashish_Mathur]

You are welcome.  If my previous reply helped, please mark that reply as Answer.

[Mark_H]

Brilliant, that did the trick - thank you!

I will check of as accepted, and have another I will raise now in a separate thread

Mark

[Ashish_Mathur]

You are welcome.  If my previous reply helped, please mark that reply as Answer.