cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shelbsassy Member
Member

change from last month

My dashboard is coming along nicely thanks to all the help I have received on the forum.  I have another question.  I want to display the percentage change from last month to this month.  Right now I have

 

Success % = DIVIDE(Sheel1[Sum Success],Sheet1[Sum Interventions],0)

 

What I have for the dates is a field called Analysis Month that so far just has July August and September data. 

 

How would I show the change from July to August?

 

Thank you!

5 REPLIES 5
Moderator v-caliao-msft
Moderator

Re: change from last month

Hi @shelbsassy,

 

You can get the previous amount by using PREVIOUSMONTH function, and then calcualte the the percentage change from last month to this month

Table 2 = SUMMARIZE('PreviousMonth','PreviousMonth'[Month])
MonthAmount = CALCULATE(SUM('PreviousMonth'[SalesAmount]),ALLEXCEPT('Table 2','Table 2'[Month]))

PreviousMonthAmount = CALCULATE(SUM('PreviousMonth'[SalesAmount]),PREVIOUSMONTH('PreviousMonth'[Date]))

PercentageChange = IF(ISBLANK('Table 2'[PreviousMonthAmount]),BLANK(),('Table 2'[PreviousMonthAmount]-'Table 2'[MonthAmount])/'Table 2'[MonthAmount])

 

Capture.PNG

 

Reference
http://bifuture.blogspot.sg/2015/09/dax-calculating-monthly-sales-changes.html

 

Regards,

Charlie Liao

 

shelbsassy Member
Member

Re: change from last month

Thank you for your reply.  I think I am missing something.  When you say Table 2 is that a calculated column or a measure?  Also when I try and do Table 2 (as a calculated column) = SUMMARIZE(PREVIOUSMONTH(Sheet1[MonthNumber])) I get an error saying that there are too few arguments, the minimum argument count for the function is 2.  Thanks!

shelbsassy Member
Member

Re: change from last month

I figured out the creating a table part.  When I create the table in data view with this expression:

 

PreviousMonth = SUMMARIZE(Sheet1,Sheet1[MonthNumber])

 

I get a list of the MonthNumbers

10

8

9

 

Whenever I try and do the PreviousMonth function it errors out on me saying too few arguments.

Moderator v-caliao-msft
Moderator

Re: change from last month

Hi @shelbsassy,

 

Whenever I try and do the PreviousMonth function it errors out on me saying too few arguments.

Please post the DAX expression you used, so that we can make further analysis.

 

Regards,

Charlie Liao

shelbsassy Member
Member

Re: change from last month

Hi there.  So I created a table called Previous Month 

Previous Month = SUMMARIZE(Sheet1,Sheet1[MonthN])

Then I created Table 2 by Summarizing that

Table 2 = SUMMARIZE('Previous Month','Previous Month'[MonthN])

Now I have 2 tables but I am not sure where to go next.  

I see the nex step in your directions is

MonthAmount = CALCULATE(SUM('PreviousMonth'[SalesAmount]),ALLEXCEPT('Table 2','Table 2'[Month])) but I don't see where the Sales Amount is coming from.  The only thing in my Table 2 is the month numbers.

I'm not sure this will work for me.  What I am trying to do is calculate the Per Member Per Month PMPM for 3 health plans and the PMPM will be different per plan per month.  Would I create columns in Table 2 with the PMPM values hard coded in for each month or how would I go about this?  THe goal is to see the PMPM change each month and I use a slicer to pick the plan and the month.

 

Thanks!