Previous Month Variance Column

[d3struckt]

Hello Mr Excel!

I just bought your PowerPivot 2010 ebook and it's an excellent resource, especially the +100,-20 chart. I used it to create a dashboard and i'm up for promotion at work! Great idea for this forum! Awesome!

My question is this. You explain date measures comparing current values to last year in the book, but I don't see a reference to the same period in the previous month. (I do a lot of month to month revenue variance analysis)

I've been trying to compare current month-to-date revenue with last-month-to-date revenue and create that variance or difference in a separate column. The problem is, my formulas are valid, but they return blank values when I include the measure in my PowerPivot table.

So column format should be:
Month 1 Revenue, Month 2 Revenue, Difference between Month 2 - Month 1, % difference from last month.

My date column is in a date format and my line total column is in a whole number format.

The formulas/measures I've created don't work and only return a blank column with no errors after formula checking.

--this formula return a blank column!
=calculate(sum(SalesOrderDetail[linetotal]),PARALLELPERIOD(SalesOrderHeader[Orderdate],-1,month))
--this formula also returns a blank column!
=calculate(sum(SalesOrderDetail[linetotal]),DateADD(SalesOrderHeader[Orderdate],-1,month))

This has been driving me crazy! Can you help me out please?

 

[powerpivotpro]

The first thing to check whenever you use the time intelligence functions: you need a separate date table. It needs to consist of one row per date, with no "missing" dates, and then relate that to your sales table (or the header table that you have).

Then the PARALLELPERIOD function should reference the date column in your date table, not the sales (or header) table.

That might be the only problem you have, but even if it's not, you have to solve that one first.

 

[greg kramer]

Nice to see the pro slumming with us!

 

[David Churchward]

As "The Pro" says, a date table is a must. It might be worth looking at DATESBETWEEN. If your related dates table also carries attributes such as prior month equivalent dates, you should be able to pick up the relevant dates for your measure.

I've got a feeling that your current measure is missing an ALL() and a VALUES() element. Your measure is already inherently filtered to the date of your records. You need to specify that the measure removes that inherent filter and then re-filters on your parallel period. It's worth checking out powerpivotpro's "Greatest Formula In The World" posts on this.

Mobile at the moment so can't be more specific. I'll take a proper look when I get back to laptop tomorrow.

 

[d3struckt]

The first thing to check whenever you use the time intelligence functions: you need a separate date table. It needs to consist of one row per date, with no "missing" dates, and then relate that to your sales table (or the header table that you have).

Then the PARALLELPERIOD function should reference the date column in your date table, not the sales (or header) table.

That might be the only problem you have, but even if it's not, you have to solve that one first.

PowerPivotPro, you are a golden god.


I would have gladly paid hard earned cash for this answer. I needed to reference the dates table instead of the original data table!

What you said worked perfectly! Thank you all for your help!!

 

[dynadad]

you mention the PowerPivot 2010 eBook. I'd like to purchase it. Where may I find it?

 

[mshparber]

Before you calculate the previous month - how do you make "current month" column so it automatically calculates the last month's sales?
I want to show it Pivot table for every salesperson (on rows) - their sales this month, previous, difference and % change
So it is very similar to yours
Thanks
Michael

 

[miguel.escobar]

You can try using the TOTALMTD for the current month and for the previous month try using the same function but in the filter section use the DATEADD(dates table here, -1,MONTH)
from those 2 measures you can then do the difference and % change quite easily.

Best!

 

[d3struckt]

you mention the PowerPivot 2010 eBook. I'd like to purchase it. Where may I find it?

It's Mr Excel's book on PowerPivot. It's in the MrExcel Store! Although I also highly recommend PowerPivotPro's book in combination to take it to the next level!