how to calculate month over month sales

[neetishrai]

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

[v-ljerr-msft · Accepted solution]

@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

[tyvell]

@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

[Ashish_Mathur]

Hi,

Your last formula looks correct.  Share the file download link.

[tyvell]

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.

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

[sudhak]

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

This is giving same month value for me. 

Can anyone please help me?

[tyvell]

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

[sudhak]

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

Unfortunately this formula is giving the same month data. 

Can anyone help please?

[neetishrai]

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

[kcantor]

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

Check this link for more information:

https://msdn.microsoft.com/en-us/library/ee634905?ui=en-US&rs=en-US&ad=US