Calculate the month-over-month

[Eriko]

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.

Eriko.

[disha_khanna]

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?

[Jmenas]

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. 

[neetishrai]

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

[Jozw126]

Should use DATE column rather than MONTH.
PrevMonthRev = CALCULATE(sum(salesdata[revenue]),PREVIOUSMONTH('date'[DATE]))

[Anonymous]

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

        )

    RETURN

        DIVIDE(SUM('20200918__Dailies'[aov]) - __PREV_DAY, __PREV_DAY)

SOLUTION:

1.  Imported a date table

2.  Joined the date and the inv_date in relationships

3.  Modified column name referenced below

WORKING EXAMPLE:

aov dod% =

    VAR __PREV_DAY =

        CALCULATE(

            SUM('20200918__Dailies'[aov]),

            PREVIOUSDAY('20200918__Dailies'[inv_date])

        )

    RETURN

        DIVIDE(SUM('20200918__Dailies'[aov]) - __PREV_DAY, __PREV_DAY)

[neetishrai]

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

[neetishrai]

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

[neetishrai]

it worked for me

http://community.powerbi.com/t5/Desktop/how-to-calculate-month-over-month-sales/m-p/63647/highlight/...

[Greg_Deckler]

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.

[procyon82 · Accepted solution]

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.