Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: Create a measure to calculate the percentage t...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted
##

ShivendooKumar

Post Patron

Create a measure to calculate the percentage that the total Sales value increased since one year ago

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-10-2019
05:55 AM

I need to create a measure to calculate the percentage that the total Sales value

increased since one year ago.

Which DAX formula should I use?

*(sum(Sales[Value])**–CALCULATE(sum(Sales[Value]),SAMEPERIODLASTYEAR(‘Date’[Date])))/CALCULATE**(sum(Sales[Value]),SAMEPERIODLASTYEAR (‘Date’ [Date]))*

*sum(Sales[Value])-CALCULATE(sum(Sales[Value]),SAMEPERIODLASTYEAR**(‘Date’ [Date]))*

*CALCULATE(sum(Sales[Value]),SAMEPERIODLASTYEAR (‘Date’[Date]))/sum(Sales[Value])*

I have a sales table and date table.

which is correct?

6 REPLIES 6

Highlighted
##

---------------------------------------

*@ me in replies or I'll lose your thread!!!*

##### I have a NEW book!

DAX Cookbook from Packt

Over 120 DAX Recipes!

**Did I answer your question? Mark my post as a solution!**

Greg_Deckler

Super User IX

Re: Create a measure to calculate the percentage that the total Sales value increased since one year

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-10-2019
06:03 AM

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

---------------------------------------

DAX Cookbook from Packt

Proud to be a Super User!

Highlighted
##

Anonymous

Not applicable

Re: Create a measure to calculate the percentage that the total Sales value increased since one year

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-10-2019
06:16 AM

Hi buddy,

i think you are on the rigth path, take a look.

Inc_Year = SUMX(Table;SUM(Table[Value])-CALCULATE(SUM(Table[Value]);SAMEPERIODLASTYEAR(Table[Date]))/SUM(Table[Value]))

you can also create a column that bring only the year of the date e then try to use this column as "filter" for your measure.

Any questions, ask 😉

Highlighted
##

alexvc

Resolver I

Re: Create a measure to calculate the percentage that the total Sales value increased since one year

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-10-2019
11:45 AM

I've used this formula:

%Inc Sales = SUM(Sales[Value])/CALCULATE(SUM(Sales[Value]),DATEADD('Date'[Date],-1,YEAR))-1

Let me know if it works

Highlighted
##

ShivendooKumar

Post Patron

Re: Create a measure to calculate the percentage that the total Sales value increased since one year

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-19-2019
05:00 AM

@Greg_Deckler wrote:See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

Please can you help selecting correct one from below 3?

*(sum(Sales[Value])**–CALCULATE(sum(Sales[Value]),SAMEPERIODLASTYEAR(‘Date’[Date])))/CALCULATE**(sum(Sales[Value]),SAMEPERIODLASTYEAR (‘Date’ [Date]))*

*sum(Sales[Value])-CALCULATE(sum(Sales[Value]),SAMEPERIODLASTYEAR**(‘Date’ [Date]))*

*CALCULATE(sum(Sales[Value]),SAMEPERIODLASTYEAR (‘Date’[Date]))/sum(Sales[Value])*

Highlighted
##

Regards,

Ashish Mathur

http://www.ashishmathur.com

https://www.linkedin.com/in/excelenthusiasts/

Ashish_Mathur

Super User V

Re: Create a measure to calculate the percentage that the total Sales value increased since one year

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-19-2019
05:12 PM

Hi,

The second one will give the correct absolute number. To convert it to %, try this

=IFERROR(sum(Sales[Value])/CALCULATE(sum(Sales[Value]),SAMEPERIODLASTYEAR(‘Date’ [Date]))-1,BLANK())

Hope this helps.

Regards,

Ashish Mathur

http://www.ashishmathur.com

https://www.linkedin.com/in/excelenthusiasts/

Highlighted
##

Sumit_Mathew

Frequent Visitor

Re: Create a measure to calculate the percentage that the total Sales value increased since one year

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-20-2020
05:26 PM

I think it will be option 1 cos the question is about percentage increase over last year = (Current Year - Last Year )/ Last Year