cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
neetishrai Frequent Visitor
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:

 

DateKeyYearMonth
120151-Jan
220151-Feb
320151-Mar
420151-Apr
520151-May
620151-Jun
720151-Jul
820151-Aug
920151-Sep
1020151-Oct
1120151-Nov
1220151-Dec
1320161-Jan
1420161-Feb
1520161-Mar
1620161-Apr
1720161-May
1820161-Jun
1920161-Jul
2020161-Aug

 

sales table 

DateKeyrevenue
1123
2234
3345
4456
5567
6678
7789
88910
991011
10101112
11111213
12121314
13231
14248
15391
16487
17598
18694
19799
208980
1 ACCEPTED SOLUTION

Accepted Solutions
v-ljerr-msft Super Contributor
Super Contributor

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:

sales.PNG

Date table:

date.PNG

Relationship between them:

relationship.PNG

The formula and result in report:

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

report.PNG

Regards

View solution in original post

9 REPLIES 9
kcantor Super Contributor
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.

Check this link for more information:

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




v-ljerr-msft Super Contributor
Super Contributor

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:

sales.PNG

Date table:

date.PNG

Relationship between them:

relationship.PNG

The formula and result in report:

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

report.PNG

Regards

View solution in original post

neetishrai Frequent Visitor
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

tyvell Frequent Visitor
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
Super User

Re: how to calculate month over month sales

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
tyvell Frequent Visitor
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.

 

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

sudhak Frequent Visitor
Frequent Visitor

Re: how to calculate month over month sales

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?

sudhak Frequent Visitor
Frequent Visitor

Re: how to calculate month over month sales

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 Frequent Visitor
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.

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 257 members 2,962 guests
Please welcome our newest community members: