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 IV

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

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!