cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## 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
Microsoft Employee

@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
Microsoft Employee

@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

@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

Hi,

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

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

This is giving same month value for me.

Frequent Visitor

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

Frequent Visitor

Unfortunately this formula is giving the same month data.

New Member

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

Community Champion

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