How to calculate a measure based based of calculation on previous year

[davidtanchon]

Hi ,

 I would like to calculate the Price gap between current year and previous year.

Price GAP Formula is  : (Product cost (Y) * Qty (Y)) - (Product cost (Y-1) * Qty (Y))

and Product cost formula is : Purchase Amount / Qty 

the detail of information is at order level, but the product cost should calculated at Item, Purchaser level and after agregated  

Is there formula dax to calculate this measure?

Thanks in advance.

[Anonymous · Accepted solution]

Hi @davidtanchon ,

Due to I don't know your data model, I build a sample to have a test.

Try this measure:

Price GAP Formula = 
VAR _CURRENTYEAR = SUM('Table'[Product cost])*SUM('Table'[QTY])
VAR _Previous = CALCULATE(SUM('Table'[Product cost])*SUM('Table'[QTY]),FILTER(ALL('Table'),'Table'[Product]=MAX('Table'[Product])&&'Table'[Year] = SUM('Table'[Year])-1))
RETURN
_CURRENTYEAR - _Previous

Result is as below.

Best Regards,
Rico Zhou

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

[Daryl-Lynch-Bzy]

Hi @davidtanchon - I think you are looking for formula the works out the ( Product Cost difference ) * Quantity within a SUMX.  I am going to assume that you Quantity is available for individual items on fact table, and the corresponding Current Year and Prior Year Price are on related product table:

The formula would look something like this:

Change in Product Cost = SUMX( fact , fact[Qty] * ( RELATED(Product[Current Cost] ) - RELATED(Product[Prior Year Cost]) )

Many thanks

Daryl

[amitchandak]

@davidtanchon , with help from date table and time intelligence you can create measures for this year and last year

example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA