Forum Discussion
Calculate the month-over-month
Hello.
There are a table "Sales" and a table "Date".
I want to do is the following.
1. Outputs Sales Amount of every month.
2. Calculate the month-over-month.
I referenced this site,but error came out.
■Site:
http://www.daxpatterns.com/time-patterns/#complete-aggregation-patterns
｢Complete Period Comparison Patterns｣
■Error Message:
A table of the value of the plural was designated, but the necessary value is only one.
How should it be done?
Please tell me.:womansad:
Eriko.
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.
10 Replies
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]))
- Jmenas
Advocate III
Thanks a lot disha_khannayou spare me so much time! thel Filter help to what i was missing! but still have some issues with the MoM comparison.
- Greg_Deckler
Community Champion
Can you post some sample data and your formula. From your error, it looks like one of your functions is returning a table when a single column is expected, or something along those lines.
procyon82Resolver I
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.
