topic How to calculate a measure based based of calculation on previous year in DAX Commands and Tips

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

davidtanchon — Mon, 07 Feb 2022 16:54:31 GMT

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.

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

amitchandak — Mon, 07 Feb 2022 17:10:28 GMT

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

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

Anonymous — Mon, 07 Feb 2022 17:14:56 GMT

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

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

Anonymous — Thu, 10 Feb 2022 10:08:12 GMT

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.