Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
neetishrai
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:

 

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
v-ljerr-msft
Employee
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:

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
v-ljerr-msft
Employee
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:

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

@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

 

 

 

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

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

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

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?

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
Community Champion
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.

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




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.