cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

Calculate the month-over-month

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:

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 please let me know what am I doing wrong?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

Re: Calculate the month-over-month

@disha_khanna

For many of the time related functions to work properly please ensure the following in the Calendar table

1. Have a column that has the FullDate in the format (DD/MM/YYYY)

2. Have a column called DateKey in the format (YYYYMMDD) taken from the FullDate

3. In your fact table build the date key also in the same format as (YYYYMMDD) using the transaction date that will be in the (DD/MM/YYYY).

4. Join the fact table and Calendar table using this datekey ( format YYYYMMDD ) from the two tables

5. To get your previous month sales just use the measure like

PMonthRevenue:=CALCULATE(sum[Revenue], PREVIOUSMONTH(Calendar[FullDate]))

6.  Now plot the Revenue and PMonthRevenue in the graph / table.

This should work.

If it works please accpet this as Solution and also give KUDOS.

Cheers

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
5 REPLIES 5
Senior Member

Re: Calculate the month-over-month

Dont think you need the filter:

If I lied, then you just need some flavor of ALL()

Super User

Re: Calculate the month-over-month

@disha_khanna

@scottsen is correct. You do not need the "FILTER" portion of the calculation.

If removing the filter reference does not work then you may want to look at the structure of your date table. In order to use time intelligence you need a date table with contingious dates (none missing). It appears you have a lot missing. You should add a complete table that is customized to your fiscal year. Then, apply months to your graph from the date table. Be sure you build your relationship based upon the date key (complete month/day/year).

http://www.powerpivotpro.com/2011/11/the-ultimate-date-table/

http://www.powerpivotpro.com/2015/02/create-a-custom-calendar-in-power-query/

Frequent Visitor

Re: Calculate the month-over-month

@scottsen : I tried to create a table that held a custom range of dates from 4/1/2013 to 4/1/2016. But still the DATEADD function does not work as expected. I tried to calculate last year's revenue (which was working previously) but it returned incorrect values. Looks like the DATEADD function isn't working. Is it something related to the context of DATEADD function?

Super User

Re: Calculate the month-over-month

@disha_khanna

For many of the time related functions to work properly please ensure the following in the Calendar table

1. Have a column that has the FullDate in the format (DD/MM/YYYY)

2. Have a column called DateKey in the format (YYYYMMDD) taken from the FullDate

3. In your fact table build the date key also in the same format as (YYYYMMDD) using the transaction date that will be in the (DD/MM/YYYY).

4. Join the fact table and Calendar table using this datekey ( format YYYYMMDD ) from the two tables

5. To get your previous month sales just use the measure like

PMonthRevenue:=CALCULATE(sum[Revenue], PREVIOUSMONTH(Calendar[FullDate]))

6.  Now plot the Revenue and PMonthRevenue in the graph / table.

This should work.

If it works please accpet this as Solution and also give KUDOS.

Cheers

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Frequent Visitor

Re: Calculate the month-over-month

Thanks a lot @CheenuSing, @kcantor and @scottsen for all your help!