Forum Discussion

M_SBS_6's avatar
M_SBS_6
Helper V
2 years ago
Solved

% diff 2 values

Hi, 

 

I have a matrix whereby I am looking at my values split by year and then day/month. Below is how the matrix is made up. 

 

What I'd like to add in is the % difference between current year vs last year. 

 

I have a filter where App_date relative in the last 12 months. 

 

Rows

App_date 

Month

Day

App_instruction

 

Columns

App_date 

Year

 

Values

Count(app_ur)

 

Year. 2023. 2024. %diff

Month

Jan. 11. 6. 45%

02. 5. 2. 60%

03. 3. 2. 33%

04. 3. 3. 0%

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi M_SBS_6 

    Maybe you can try this:

    I create a set of sample data:

    Then create a measure:

    DIFF = 
	VAR _currentMonth = MONTH(MAX('Table'[date]))
	VAR _currentYear = YEAR(MAX('Table'[date]))
	VAR _PreviousYear = CALCULATE(
		YEAR(MAX('Table'[date])),
		FILTER(
			ALLSELECTED('Table'),
			YEAR('Table'[date]) < _currentYear
		)
	)
	VAR _perviousValue = CALCULATE(
		SUM('Table'[value]),
		FILTER(
			ALLSELECTED('Table'),
			YEAR('Table'[date]) = _PreviousYear && MONTH('Table'[date]) = _currentMonth
		)
	)
	RETURN
		IF(
			_perviousValue <> BLANK(),
			(_perviousValue - SUMX(
				'Table',
				'Table'[value]
			)) / _perviousValue
		)

    The result is as follow:

     

    Best Regards,

    Zhengdong Xu

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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi M_SBS_6 

    Maybe you can try this:

    I create a set of sample data:

    Then create a measure:

    DIFF = 
	VAR _currentMonth = MONTH(MAX('Table'[date]))
	VAR _currentYear = YEAR(MAX('Table'[date]))
	VAR _PreviousYear = CALCULATE(
		YEAR(MAX('Table'[date])),
		FILTER(
			ALLSELECTED('Table'),
			YEAR('Table'[date]) < _currentYear
		)
	)
	VAR _perviousValue = CALCULATE(
		SUM('Table'[value]),
		FILTER(
			ALLSELECTED('Table'),
			YEAR('Table'[date]) = _PreviousYear && MONTH('Table'[date]) = _currentMonth
		)
	)
	RETURN
		IF(
			_perviousValue <> BLANK(),
			(_perviousValue - SUMX(
				'Table',
				'Table'[value]
			)) / _perviousValue
		)

    The result is as follow:

     

    Best Regards,

    Zhengdong Xu

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

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

    Do not include sensitive information or anything not related to the issue or question.

    If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

    Please show the expected outcome based on the sample data you provided.

    Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523