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

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:

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

Date table:

Relationship between them:

The formula and result in report:

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

Regards

9 REPLIES 9
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:

Date table:

Relationship between them:

The formula and result in report:

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

Regards

Frequent Visitor

@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

Hi,

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

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

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

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?

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.

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?

New Member

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

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

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Power BI Monthly Update - July 2024

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

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors