Forum Discussion
Calculate the month-over-month
Just tried something like this which gives me the previous month
Previous month = CALCULATE(SUM('GA Ecommerce'[Sales]),PREVIOUSMONTH('GA Ecommerce'[Date]))
or
Previous Month = CALCULATE(SUM('GA Ecommerce'[Sales]),DATEADD('GA Ecommerce'[Date],-1,MONTH))
Should be pretty easy to do MOM from this.
I'm facing an issue while calculating previous month sales for the following data:
| TimeID | Date | FY | FYQuarter | Month |
| 1 | 4/1/2013 | 2013 | 1 | April |
| 2 | 4/1/2014 | 2014 | 1 | April |
| 3 | 4/1/2015 | 2015 | 1 | April |
| 4 | 8/1/2013 | 2013 | 2 | August |
| 5 | 8/1/2014 | 2014 | 2 | August |
| 6 | 8/1/2015 | 2015 | 2 | August |
| 7 | 12/1/2013 | 2013 | 3 | December |
| 8 | 12/1/2014 | 2014 | 3 | December |
| 9 | 12/1/2015 | 2015 | 3 | December |
| 10 | 2/1/2014 | 2013 | 4 | February |
| 11 | 2/1/2015 | 2014 | 4 | February |
| 12 | 2/1/2016 | 2015 | 4 | February |
| 13 | 1/1/2014 | 2013 | 4 | January |
| 14 | 1/1/2016 | 2015 | 4 | January |
| 15 | 7/1/2013 | 2013 | 2 | July |
| 16 | 7/1/2014 | 2014 | 2 | July |
| 17 | 7/1/2015 | 2015 | 2 | July |
I simply have the month and Year information. The day information was not available in th raw data and the 1st day in every date is added by me. This Date table stores the Fiscal year that begins in April.
I have calculated the revenue using Sales from a Fact Table. I now want to calculate the previous month's revenue. I used the DATEADD Function as shown below:
LastMonthRev = CALCULATE([Revenue],FILTER(DimTime,DATEADD(DimTime[Date],-1,MONTH)))
However I get the same value as this month's revenue(refer the table shown below)
| Month | Revenue | LastMonthRev |
| 1 | $10.00 | $10 |
| 2 | $20.00 | $20 |
| 3 | $30.00 | $30 |
| 4 | $40.00 | |
| 5 | $50.00 | $50 |
| 6 | $60.00 | $60 |
| 7 | $70.00 | $70 |
| 8 | $80.00 | $80 |
| 9 | $90.00 | $90 |
| 10 | $100.00 | $100 |
| 11 | $110.00 | $110 |
| 12 | $120.00 | $120 |
This data is just for one year. Can someone help me in performing month over month calculations or just getting the last month's revenue correctly?
did anybody get the solution for this
i am using PrevMonthRev = CALCULATE(sum(salesdata[revenue]),PREVIOUSMONTH('date'[Month])) but it is not giving any data
- Should use DATE column rather than MONTH.
PrevMonthRev = CALCULATE(sum(salesdata[revenue]),PREVIOUSMONTH('date'[DATE]))
Thank you for suggesting this, I made a change that worked for calculating the day over day using this methodology:
NON-WORKING EXAMPLE:
aov dod% =VAR __PREV_DAY =CALCULATE(SUM('20200918__Dailies'[aov]),PREVIOUSDAY('20200918__Dailies'[inv_date].[date]))RETURNDIVIDE(SUM('20200918__Dailies'[aov]) - __PREV_DAY, __PREV_DAY)SOLUTION:1. Imported a date table2. Joined the date and the inv_date in relationships3. Modified column name referenced belowWORKING EXAMPLE:
aov dod% =VAR __PREV_DAY =CALCULATE(SUM('20200918__Dailies'[aov]),PREVIOUSDAY('20200918__Dailies'[inv_date]))RETURNDIVIDE(SUM('20200918__Dailies'[aov]) - __PREV_DAY, __PREV_DAY)
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
