Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to Solution.
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
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
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.
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))
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?
" 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.
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
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
Proud to be a Super User!