Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
We finally figured it out:
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], FILTER(ALLSELECTED('Revenue by Product'), 'Revenue by Product'[Period ID]=PreviousPeriod))
VAR Result = DIVIDE (CY,PY)
RETURN (IF(NOT(ISBLANK(Result)) && ISFILTERED('Revenue by Product'[Period]), Result-1))
Hi,
Share the download link of your PBI file.
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)
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)
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?
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?
There is no separate dates table, the dates are in the column "Year/Month" in the previous reply. This is the matrix:
Oh ok, and Period ID (not Period) has which values?
Period ID is from 1 to 7
Period is 1H2019...1H2022
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)
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?
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
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)
What does "See details" say? 🙂
It says this
Ok then your Period ID is not of type "Integer" (or Whole number), please fix it (maybe in PowerQuery). Then it should work.
I have changed but it seems it only return for 1H 2019
That doesn't make sense to me, can you show the matrix again?
This is the matrix for the calculated formula you gave
Matrix for period
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)
The -1 in VAR PY is outisde the bracket?
i edited the post, it should not be in the VAR PY line...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
101 | |
78 | |
70 | |
63 |
User | Count |
---|---|
141 | |
106 | |
101 | |
85 | |
72 |