cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Patron
Post Patron

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

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
Super User IX
Super User IX

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

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...


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

@ 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!

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

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
Resolver I
Resolver I

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

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
Post Patron
Post Patron

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


@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
Super User V
Super User V

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

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
Frequent Visitor

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

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

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors