How to calculate avg price for the same period last year for sum totals in Dax

[bhickok]

I have a table of Invoices that are for different products over multiple years. I am attempting to get the previous years avg price for each item by dividing the Calculate(Sum(sales)/Sum(qtyShipped),SamePeriodLastYear(DateDim[Date]))
When I do this calculation I get NaN returned. If I change the SamePeriodLastYear to ParallelPeriod(DateDim[Date],-12,Month) I get a 0 value. 

In Power BI i'm building the Matrix by 

Rows: 

Item

Month

Columns:

Year

Values:

Qty Shipped

Total Sales

Avg Price (Totalsales/qtyshipped)

Previous Years avg Price (I want this to be the samePeriodlastyear of the totalSales/Qtyshipped)

There is one more hiccup in here. If the previous year has a 0 value for the avg price I want it to use the current year as the pervious years avg price.  This is an example of the table I am attempting to return in powerbi except with a column that will in the previous years avg price. These number are not correct it is just a dummy data table. 

[Anonymous · Accepted solution]

Hi @bhickok ,

You need to divide previous year's sales by previous year's qtyshipped in the formula.

Here's an example.

_presale returns the previous year's sales.

Measure = var _presale=CALCULATE(SUM('Table'[sale]),FILTER(ALLSELECTED('Table'),[item number]=MAX('Table'[item number])&&[Month]=MAX('Table'[Month])&&[Year]=MAX('Table'[Year])-1))
return _presale

_preqty returns the previous year's qryshipped.

Measure = var _presale=CALCULATE(SUM('Table'[sale]),FILTER(ALLSELECTED('Table'),[item number]=MAX('Table'[item number])&&[Month]=MAX('Table'[Month])&&[Year]=MAX('Table'[Year])-1))
var _preqty=CALCULATE(SUM('Table'[qtyshipped]),FILTER(ALLSELECTED('Table'),[item number]=MAX('Table'[item number])&&[Month]=MAX('Table'[Month])&&[Year]=MAX('Table'[Year])-1))
return _preqty

Finally, divide the two.

Measure = var _presale=CALCULATE(SUM('Table'[sale]),FILTER(ALLSELECTED('Table'),[item number]=MAX('Table'[item number])&&[Month]=MAX('Table'[Month])&&[Year]=MAX('Table'[Year])-1))
var _preqty=CALCULATE(SUM('Table'[qtyshipped]),FILTER(ALLSELECTED('Table'),[item number]=MAX('Table'[item number])&&[Month]=MAX('Table'[Month])&&[Year]=MAX('Table'[Year])-1))
return DIVIDE(_presale,_preqty)

Hope the above example helps you.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.