New measure for delta between two years

[NewPowerUser198]

Hello everyone i am struggling with a simple issue on Power BI desktop.

I created a matrix like this where both description and amount have a reference date in column (the two cells are obviously together),  i need to do the differences between the two years using a new measure and where for product A i expect 30 as delta amount and 2 as delta item sold.

Thank you very much for your time!

		2023	2023	2024	2024
PRODUCT	DESCRIPTION	AMOUNT	ITEMS SOLD	AMOUNT	ITEMS SOLD
A	ALFA	40	1	70	3
B	BETA	50	2	80	4
C	GAMMA	60	3	90	5

[talespin · Accepted solution]

hi @NewPowerUser198 

Please add these measures to matrix values.

Amount Difference =

VAR _Year = SELECTEDVALUE(Foglio1[date].[Anno])

VAR _PrevDate = CALCULATE( MAX(Foglio1[date]), REMOVEFILTERS(Foglio1), YEAR(Foglio1[date]) < _Year)

VAR _CurrYrAmount = CALCULATE( SUM(Foglio1[amount]) )

VAR _PrevYrAmount = CALCULATE( SUM(Foglio1[amount]), REMOVEFILTERS(Foglio1[date].[Anno]), Foglio1[date] = _PrevDate )

RETURN IF( ISBLANK(_PrevYrAmount), BLANK(), _CurrYrAmount - _PrevYrAmount)

Client ID Difference =

VAR _Year = SELECTEDVALUE(Foglio1[date].[Anno])

VAR _PrevDate = CALCULATE( MAX(Foglio1[date]), REMOVEFILTERS(Foglio1), YEAR(Foglio1[date]) < _Year)

VAR _CurrYrcount = CALCULATE( COUNT(Foglio1[id_client]) )

VAR _PrevYrcount = CALCULATE( COUNT(Foglio1[id_client]), REMOVEFILTERS(Foglio1[date].[Anno]), Foglio1[date] = _PrevDate )

RETURN IF( ISBLANK(_PrevYrcount), BLANK(), _CurrYrcount - _PrevYrcount)

[NewPowerUser198]

Thank you, it works.

Last question: how do I modify the code if i will have consecutive dates during the year? 

Let me explain, if the data will become more frequent with 3/4 monthly observations not yearly?

Thank you

[talespin]

hi @NewPowerUser198 

Please try this

Amount Difference =

VAR _Year = SELECTEDVALUE(Foglio1[date].[Anno])

VAR _PreviousYear = _Year - 1

VAR _CurrYrAmount = CALCULATE( SUM(Foglio1[amount]) )

VAR _PrevYrAmount = CALCULATE( SUM(Foglio1[amount]), REMOVEFILTERS(Foglio1[date].[Anno]), Foglio1[date].[Anno] = _PreviousYear)

RETURN IF( ISBLANK(_PrevYrAmount), BLANK(), _CurrYrAmount - _PrevYrAmount)

A better solution would be to create a date table. Example

Step1 Create a Date table 

Calendar =

VAR _MinDate = MIN(Foglio1[date])

VAR _MaxDate = MAX(Foglio1[date])

RETURN CALENDAR(_MinDate, _MaxDate)

Step2 : Mark it as Date table and join it to your data table.

Step3 : Create this measure. And use Year from calendar table in your matrix.

Amount Difference =

VAR _Year = SELECTEDVALUE('Calendar'[Year])

VAR _PreviousYear = _Year - 1

VAR _CurrYrAmount = CALCULATE( SUM(Foglio1[amount]) )

VAR _PrevYrAmount = CALCULATE( SUM(Foglio1[amount]), REMOVEFILTERS('Calendar'[Year]), 'Calendar'[Year] = _PreviousYear)

RETURN IF( ISBLANK(_PrevYrAmount), BLANK(), _CurrYrAmount - _PrevYrAmount)

[talespin]

hi @NewPowerUser198 

Need some modification to code, will get back.

[NewPowerUser198]

Do you need a new pbix file? 

[talespin]

hi @NewPowerUser198 

First check the solution which I just shared and if it doesn''t work or I did not understand your requirement, please share pbix file.

[nareshr89]

Can you shed some more light on this.. Will the years be dynamic? Also if you could share a sample PBIX file it would help to figure out a solution.

[NewPowerUser198]

Sample 

Here is the pbix file. Thank you!

[Anonymous]

Hi, @NewPowerUser198 

Based on the PBIX file you provided, you can calculate Amount between two years and Id_client between two years through DAX below.

DAX formula:

Amount between two years = 
var _Year=YEAR(CALCULATE(MIN('Foglio1'[date])))
VAR _a = CALCULATE(SUM(Foglio1[amount]),FILTER('Foglio1',YEAR('Foglio1'[date])=_Year&&'Foglio1'[product]='Foglio1'[product]))
VAR _b = CALCULATE(SUM('Foglio1'[amount]),FILTER('Foglio1',YEAR('Foglio1'[date]) =_Year+1&&'Foglio1'[product]='Foglio1'[product]))
RETURN _b -_a

Id_client between two years = 
var _Year=YEAR(CALCULATE(MIN('Foglio1'[date])))
VAR _a = CALCULATE(COUNTROWS('Foglio1'),FILTER('Foglio1',YEAR('Foglio1'[date])=_Year&&'Foglio1'[product]='Foglio1'[product]))
VAR _b = CALCULATE(COUNTROWS('Foglio1'),FILTER('Foglio1',YEAR('Foglio1'[date]) =_Year+1&&'Foglio1'[product]='Foglio1'[product]))
RETURN _b -_a

Use Table visual to reflect the results:

I have provided the PBIX file used this time below. I hope this helps. Let me know if you have any further questions.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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

[NewPowerUser198]

Thank you for your time and your help, i need to show the result on the same table i provided. If I understood well this works with a new table, is it correct?