topic Re: New measure for delta between two years in DAX Commands and Tips

New measure for delta between two years

NewPowerUser198 — Thu, 15 Feb 2024 09:05:55 GMT

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

Re: New measure for delta between two years

nareshr89 — Thu, 15 Feb 2024 13:10:38 GMT

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.

Re: New measure for delta between two years

NewPowerUser198 — Thu, 15 Feb 2024 14:29:24 GMT

Sample

Here is the pbix file. Thank you!

Re: New measure for delta between two years

Anonymous — Fri, 16 Feb 2024 08:08:17 GMT

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.

Re: New measure for delta between two years

NewPowerUser198 — Fri, 16 Feb 2024 08:30:25 GMT

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?

Re: New measure for delta between two years

talespin — Sat, 17 Feb 2024 11:11:53 GMT

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)

Re: New measure for delta between two years

NewPowerUser198 — Mon, 19 Feb 2024 09:05:05 GMT

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

Re: New measure for delta between two years

talespin — Mon, 19 Feb 2024 09:17:09 GMT

hi NewPowerUser198

Need some modification to code, will get back.

Re: New measure for delta between two years

NewPowerUser198 — Mon, 19 Feb 2024 09:23:49 GMT

Do you need a new pbix file?

Re: New measure for delta between two years

talespin — Mon, 19 Feb 2024 10:17:51 GMT

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)

Re: New measure for delta between two years

talespin — Mon, 19 Feb 2024 10:23:21 GMT

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.