Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

In Excel Power Pivot, calculate month-over-month growth using a measure and DAX

Posted on 2014-04-22
1
Medium Priority
?
4,408 Views
Last Modified: 2014-06-03
In my Excel Power Pivot table I have a couple columns:

UsageMonth  (date datatype)
Consumed    (float number)

I want to calculate month-over-month growth. For example, if in January I consumed 100 and in February I consumed 110, 110/100-1 equals .10 or 10% growth.

If my table has this data:

UsageMonth   Consumed
1/1/2014          100
2/1/2014           110

I am thinking I should be able to create, in DAX, a measure named ConsumedPriorMonth. How do I do it?
0
Comment
Question by:RickInBellevue
1 Comment
 

Accepted Solution

by:
RickInBellevue earned 0 total points
ID: 40109078
Okay, I figured out the answer to my own question.

To calculate month-over-month growth, the fundamental formula that underlies the solution is:

(Month2Usage - Month1Usage)/Month1Usage

The DAX-based answer has two parts:

Part 1: Create a measure that calculates the prior month's usage:

PriorMonthUsage:=CALCULATE(SUM(Usage),DATEADD(UsageData[UsageMonth],-1,MONTH))


Part 2:  Compute month-over-month growth:

MoMGrowth:=IF([PriorMonthUsage],(SUM([Usage])-[PriorMonthUsage])/[PriorMonthUsage],BLANK())

The IF/BLANK parts handles the case where there is no usage.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Meetings to discuss business process can waste time, and often do .  The meeting's dialog can get confusing when participants have different professional perspectives and backgrounds.  A jointly-developed process picture helps wade through the confu…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This video walks the viewer through the process of creating a watermark for their document, customizing it, and saving it for viewing/printing needs.
This video walks the viewer through the process of creating an MLA formatted document, as well as a bibliography with citations.

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question