cancel
Showing results for
Did you mean:

## Change% year over year , negative sales amount returns wrong result

Hello Team,

I have this code below that calculates the change of sales percentage between three years, it works very well but I have an issue where if there is a credit or in other words negative sales amount, the change % returns negative value even which is not right.

For example, lets stay on Dec 20th 2020, we had -\$10,000 and in 2021, we have \$15,000. The change in percantage formula returns this result -33.3% but we acutally made more sales in 2021 so why is it showin negative value?

Code:

Change% =
IF(
ISFILTERED('Calendar'[Date]),
VAR __PREV_YEAR =
CALCULATE(
SUM('Sales'[Total Sales]),
)
RETURN
DIVIDE(SUM('Sales'[Total Sales]) - __PREV_YEAR,__PREV_YEAR)
)

Any help is highly appreciated, thanks a lot

6 REPLIES 6
Super User

@Mohanad-Mustafa , if year is not in context, then it means full data vs 1year less data

You can try options like

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

//Only year vs Year, not a level below

This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!

@amitchandak , thanks for your reply. I am trying to calcualte the percentage change , thats why I used variables and division to get the percentage, now do you think if I use the absolute function, that should get rid of the - sign in my code?

Community Support

I think absolute function can help you get rid of the - sign.

Try this code.

Change% =
IF (
ISFILTERED ( 'Calendar'[Date] ),
VAR __PREV_YEAR =
CALCULATE (
SUM ( 'Sales'[Total Sales] ),
DATEADD ( 'Calendar'[Date], -1, YEAR )
)
RETURN
ABS ( DIVIDE ( SUM ( 'Sales'[Total Sales] ) - __PREV_YEAR, __PREV_YEAR ) )
)

Best Regards,
Rico Zhou

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

Thanks for your reply, yes it did but the problem its not showing the negative sign when we have less sales year over year and the team wants to see the negative sign when the result it less. So unfortuatnetly, the abs sign doesn't help me in this case

Community Support

I think you can add a If function to show negative result if sales in current year is less than sales in previous year.

My Sample:

New Measure:

Change% =
IF (
ISFILTERED ( 'Calendar'[Date] ),
VAR __PREV_YEAR =
CALCULATE (
SUM ( 'Sales'[Total Sales] ),
DATEADD ( 'Calendar'[Date], -1, YEAR )
)
VAR _CUR_YEAR =
SUM ( 'Sales'[Total Sales] )
VAR _DIFF = _CUR_YEAR - __PREV_YEAR
VAR _DIVIDE =
DIVIDE ( _DIFF, __PREV_YEAR )
RETURN
IF ( _DIFF > 0, ABS ( _DIVIDE ), ABS ( _DIVIDE )*-1)
)

Result is as below.

Current Year: 2021/12/20 15000, Previous Year: 2021/12/21 10000

Current Year: 2021/12/21 10000, Previous Year: 2021/12/21 15000

Best Regards,
Rico Zhou

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

Thanks for the effort and your kind reply, but this is didn'twork for me unfortuantely, when I used your code, I don't get any results. Unfortuantely, I can't share the data with you as it is sensitive.

So the result is empty when I use your code, I am showing the sales over three years using Matrix visual dashboard in Power BI so the user can see the sales year over year easily in a simple table where the client accounts are in rows and the sales amounts and years in columns.

Announcements

#### The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

#### Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!