Percentage Year on Year

[rydajo96]

Hi I have the follwoing chart and I want to calculate the percentage change between the bars e.g. 1H 2019 and 2H 2019, 2H 2019 and 1H 2020. 

What formula should I use? Please help >< Thank you in advance 

[Ashish_Mathur]

Hi,

Share the download link of your PBI file.

[lukiz84]

You sure its YoY? 😉 Looks like HY to HY 🙂 See:

Change 6M % =
   VAR Current = [yourMeasure]
   VAR Previous = 
      CALCULATE(
          [yourMeasure],
          DATEADD(yourDateTable[Date], -6, MONTH)
      )
 
   RETURN DIVIDE(Current, Previous)

[rydajo96]

Hello

Thank for the formula, unforuntately I didnt get the output,. It's returning all 100% but with the following line and is not in the right direction:

This is the formula I used:

 Half Yearly Change =

VAR CY = [Revenue]

VAR PY = CALCULATE([Revenue], DATEADD('Revenue by Product'[Year/Month].[Date],-6,MONTH) )

RETURN DIVIDE (CY,PY) 

 

The formula for revenue is: 

Revenue = -sum('Revenue by Product'[Sum_Amount]) 

This is the data I have (unable to share the notebook due to NDA):

Acct Name	Product Cat	Year/Month	Customer	Sum Amount	Period	Period ID
ABC	XYZ	Friday, 1 Mar 2019	123	299	1H 2019	1

Is there a way I can make use of the period ID?

[lukiz84]

Do you have a date table in place?

Can you put the Periods in a matrix and add the Half Year Change so i can see the values?

[rydajo96]

There is no separate dates table, the dates are in the column "Year/Month" in the previous reply. This is the matrix:

[lukiz84]

Oh ok, and Period ID (not Period) has which values?

[rydajo96]

Period ID is from 1 to 7 

Period is 1H2019...1H2022

[lukiz84]

Ok, in general i would suggest you implement a date table to be able to use the time intelligence funtions like DATEADD... it's not possible in your scenario. Anyway this should work:

Change 6M % =
   VAR CurrentPeriod = SELECTEDVALUE(table[Period ID])
   VAR PreviousPeriod = IF(CurrentPeriod > 1, CurrentPeriod - 1, 1)

   VAR Current = [Revenue]
   VAR Previous = 
      CALCULATE(
          [Revenue],
          table[Period ID] = PreviousPeriod
      )
 
   RETURN DIVIDE(Current, Previous)

[rydajo96]

I am trying to tweak this formula as it is returning a similar output but not the exact output 

Revenue % difference from Revenue = 
VAR __BASELINE_VALUE = 	CALCULATE(
		[Revenue],
		'Revenue by Product'[Period] IN {"1H 2020"},
		ALL('Revenue by Product'[Period ID])
	)
VAR __VALUE_TO_COMPARE = CALCULATE([Revenue], DATEADD('Revenue by Product'[Year/Month].[Date],-6,MONTH))
RETURN
	IF(
		NOT ISBLANK(__VALUE_TO_COMPARE),
		DIVIDE(__VALUE_TO_COMPARE - __BASELINE_VALUE, __BASELINE_VALUE)
	)

However, it is using "1H 2020" as the base. Is there anyway I can tweak the formula to make it change from one period to another?

[lukiz84]

no need, just use the code i provided above (the new one). you can't use dateadd because you don't have a date table

[rydajo96]

I got this error

This is the formula I used per your reply

Half Yearly Change = 
    VAR CurrentPeriod = SELECTEDVALUE('Revenue by Product'[Period ID])
    VAR PreviousPeriod = IF(CurrentPeriod > 1, CurrentPeriod - 1, 1)
    
    VAR CY = [Revenue]
    VAR PY = CALCULATE([Revenue], 'Revenue by Product'[Period ID]=PreviousPeriod)
RETURN DIVIDE (CY,PY) 

[lukiz84]

What does "See details" say? 🙂

[rydajo96]

It says this

[lukiz84]

Ok then your Period ID is not of type "Integer" (or Whole number), please fix it (maybe in PowerQuery). Then it should work.

[rydajo96]

I have changed but it seems it only return for 1H 2019

[lukiz84]

That doesn't make sense to me, can you show the matrix again?

[rydajo96]

This is the matrix for the calculated formula you gave

Matrix for period

[lukiz84]

Ah sorry,

Half Yearly Change = 
    VAR CurrentPeriod = SELECTEDVALUE('Revenue by Product'[Period ID])
    VAR PreviousPeriod = IF(CurrentPeriod > 1, CurrentPeriod - 1, 1)
    
    VAR CY = [Revenue]
    VAR PY = CALCULATE([Revenue], 'Revenue by Product'[Period ID]=PreviousPeriod)
    VAR Result = DIVIDE (CY,PY) 
    RETURN (IF(NOT(ISBLANK(Result)), Result-1)

[rydajo96]

The -1 in VAR PY is outisde the bracket?

[lukiz84]

i edited the post, it should not be in the VAR PY line...