cancel
Showing results for
Did you mean:
Frequent Visitor

how to calculate month over month sales

i am trying to create month over month sales, but unable to calculate the previous month sales

PrevMonthRev = CALCULATE(sum(salesdata[revenue]),PREVIOUSMONTH('date'[Month]))

date table:

 DateKey Year Month 1 2015 1-Jan 2 2015 1-Feb 3 2015 1-Mar 4 2015 1-Apr 5 2015 1-May 6 2015 1-Jun 7 2015 1-Jul 8 2015 1-Aug 9 2015 1-Sep 10 2015 1-Oct 11 2015 1-Nov 12 2015 1-Dec 13 2016 1-Jan 14 2016 1-Feb 15 2016 1-Mar 16 2016 1-Apr 17 2016 1-May 18 2016 1-Jun 19 2016 1-Jul 20 2016 1-Aug

sales table

 DateKey revenue 1 123 2 234 3 345 4 456 5 567 6 678 7 789 8 8910 9 91011 10 101112 11 111213 12 121314 13 231 14 248 15 391 16 487 17 598 18 694 19 799 20 8980
1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft

Re: how to calculate month over month sales

@neetishrai

As @kcantor has mentioned above, most of the time intelligence functions require a standard Date table to work correctly. In your scenario, you can use CALENDAR function to create the Date table. Moreover, you need to add a Date column in your sales table and create a relationship using this column instead of the non-Date column(in your case, you're using DateKey which is a non-Date column to relate your date table and sales table).

The Month Over Month sample below is for your reference.

Sales table:

Date table:

Relationship between them:

The formula and result in report:

`PMSalse = CALCULATE([Total Sales], PREVIOUSMONTH('Date'[Date]))`

Regards

9 REPLIES 9
Super Contributor

Re: how to calculate month over month sales

@neetishrai

Month over Month is usually calculated by subtracting either one month or thirty days using time intelligence within PowerBI. In order for that to work you need a date table with all dates included. You appear to have a non standard date table and I would wager a guess that is why it isn't working.

Proud to be a Datanaut!

Microsoft

Re: how to calculate month over month sales

@neetishrai

As @kcantor has mentioned above, most of the time intelligence functions require a standard Date table to work correctly. In your scenario, you can use CALENDAR function to create the Date table. Moreover, you need to add a Date column in your sales table and create a relationship using this column instead of the non-Date column(in your case, you're using DateKey which is a non-Date column to relate your date table and sales table).

The Month Over Month sample below is for your reference.

Sales table:

Date table:

Relationship between them:

The formula and result in report:

`PMSalse = CALCULATE([Total Sales], PREVIOUSMONTH('Date'[Date]))`

Regards

Frequent Visitor

Re: how to calculate month over month sales

thanks @v-ljerr-msft @kcantor

You are right the issue was me creating a date table. But once i created using the calendar function it worked

Frequent Visitor

Re: how to calculate month over month sales

@v-ljerr-msft

This isn't working for me. I've created a date table there's dates in my data but all that is returned is null or the same data for current month. please help! I've tried the several functions.

Previous Month = CALCULATE(sum('working data'[Qty]),FILTER('working data',DATEADD('Datetable'[DATE],-1,MONTH)))

result = same as current month

Previous Month2 = if(isempty(PREVIOUSMONTH('datetable'[Date])),0,CALCULATE(SUM('working data'[Qty])))

result = 0

Previous Month3 = CALCULATE(SUM('working data'[Qty]), PREVIOUSMONTH('Datetable'[Date]))

result = null/blank

Super User

Re: how to calculate month over month sales

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

Re: how to calculate month over month sales

I was actually able to get the first formula to work by eliminating the FILTER function! I spent approx. 20 hrs on this...ugh! Now moving on to Year over year.... Hopefully this helps someone else.

Frequent Visitor

Re: how to calculate month over month sales

Unfortunately this formula is giving the same month data.

Frequent Visitor

Re: how to calculate month over month sales

This is giving same month value for me.

Frequent Visitor

Re: how to calculate month over month sales

" Make sure the dates you are using in your visualizations are from your "Datetable" table. I was getting the same results when I used the dates or months from my "raw data" table. Try that and let me know if it works.

Announcements

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (1,210)