Skip to main content
cancel
Showing results for 
Search instead 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

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

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

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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