Hello,
I have completed both the Power Query and Power Pivot courses and have found them both extremely interesting.
My biggest dilemma is that the management team in this company want to start reporting using Moving annual total (MAT) - 3 years. They want to see the sales, GP $ and % for products, over the course of the previous 36 months. They want this to be a rolling yearly sum, so changes at the end of each month with data from the new month added to the total and data from the first month of the period taken away.
I am using a date table from my ERP and have had a look at the TimeIntelligence Functions "Previous" and "next" however I don't know how to get it all to work. Is there anything you could point me to that might help me step through this.
I seem to have no problem getting my data exactly how I want it for the financial or calendar years, the rolling months are causing me some issues. Should I look to making the date table dynamic or keep trying to work it out in power pivot?
Any assistance you can provide will be much appreciated
Thank you
Maree
Hi Maree,
You can write a measure like this:
Moving Annual Total: = CALCULATE( SUM(TransactionData[Sales]), DATESINPERIOD(CalenderTable[Date],LASTDATE(CalenderTable[Date]),-3,YEAR) )
Hope that helps.
Mynda
Sorry Mynda, I don't understand. I tried to convert to my tables and I got an #ERROR (see below for what I wrote)
=CALCULATE(SUM(SP_BI_InventorySalesTransactionView[SalesValue]),DATESINPERIOD(DateDimensions[TxDate],LASTDATE(DateDimensions[TxDate],-3,YEAR())))
I want to see something like this:
Will this formula provide the aggregation to allow this view?
Thank you
MAree
Hi Maree,
The YEAR part of the formula doesn't have parentheses. Please look at the formula I gave you again, you'll see it only has two closing parentheses after YEAR.
Mynda
Whoops, I fixed that but still getting an #ERROR
It keeps adding an additional closing bracket on the end? What am I doing wrong?
Sales PY:=CALCULATE(SUM(SP_BI_InventorySalesTransactionView[SalesValue]),DATESINPERIOD(DateDimensions[TxDate],LASTDATE(DateDimensions[TxDate],-3,YEAR)))
In addition to this to get last year I am using the below formulas:
Sales LY:=CALCULATE(SUM(SP_BI_InventorySalesTransactionView[SalesValue]),SAMEPERIODLASTYEAR(DateDimensions[TxDate]))
GP $ LY:=SUM(SP_BI_InventorySalesTransactionView[SalesValue]),SAMEPERIODLASTYEAR(DateDimensions[TxDate])-SUM(SP_BI_InventorySalesTransactionView[CostValue]),SAMEPERIODLASTYEAR(DateDimensions[TxDate])
To get the prior year, 2020 - 2 years by month should I then be using the Year formula you gave provided to get this data?
Sorry about all of the questions I am well and truly out of my depth and am wondering if I am better to spend my time working out the get pivot data formulas to get the layout they are requesting.
Thank you
Maree
You're missing the closing parenthesis for the LASTDATA function, see mine:
Moving Annual Total: = CALCULATE( SUM(TransactionData[Sales]), DATESINPERIOD(CalenderTable[Date],LASTDATE(CalenderTable[Date]),-3,YEAR) )
Mynda
Yay, that worked but it isn't actually the value I am after.
I want to be able to drop in a fx that shows the "SamePeriodLastYear" for another year back...
For example:
In the columns Months > May| June| July| Aug| Sep| Oct|....|April | Grand Total
TY Sales
TY GP
TY GP%
LY Sales - I'm getting this using "SAME PERIOD LAST Year" function
Sales LY:=CALCULATE(SUM(SP_BI_InventorySalesTransactionView[SalesValue]),SAMEPERIODLASTYEAR(DateDimensions[TxDate]))
LY GP - Below is my formula for this one:
GP $ LY:=CALCULATE(SUM(SP_BI_InventorySalesTransactionView[SalesValue]),SAMEPERIODLASTYEAR(DateDimensions[TxDate]))-CALCULATE(SUM(SP_BI_InventorySalesTransactionView[CostValue]),SAMEPERIODLASTYEAR(DateDimensions[TxDate]))
LY GP% - I'm still trying to work this one out.
LY-1 Sales - I don't know how to get this? Do I change the YEAR on your formula above suggestion to MONTH?
LY - 1 GP
LY - 1 GP%
Sorry if this is a weird request my GM wants to see a rolling 3 years by month at various levels of detail for our business and I'm clearly clueless.
Thank you
Maree
Hi Maree,
It's very difficult to picture in my head what you want and I don't know what you mean by 'fx' in this sentence:
"I want to be able to drop in a fx that shows the "SamePeriodLastYear" for another year back..."
Please try changing YEAR to MONTH and see if that helps. You'll also need change the number from -3 to however many months you want to move back, plus you'll need months in your PivotTable if you want to see the values at that level of context, which it sounds like you have.
Mynda
sorry, fx was me trying to say "function"...
The month formula is still returning a number that doesn't make sense. See the attached screenshot - I should be getting a figure of around $1.5M for this segregation of data but I am getting $46.8M.
The month formula I have entered says this:
Sales ($) PY:=CALCULATE(SUM(SP_BI_InventorySalesTransactionView[SalesValue]),DATESINPERIOD(DateDimensions[TxDate],LASTDATE(DateDimensions[TxDate]),-24,MONTH) )
In the 3rd line of the pivot table: "Sales ($) PY:" I am expecting May 2017 results.... Which isn't $46m but closer to $1.5M
Does this make sense?
Thank you
Maree
Nope, sorry! I can't see the year field in the PivotTable to know what the filter context is for those measures. Can you please give me a bigger screenshot with all the relevant information that contributes to the filters affecting the measures?
Also, what's the difference between LY (last year) and PY (prior year), technically these are the same thing but presumably you're using them to represent different periods. e.g. is Last Year 2019 and PY 2018?
Reading back through some of your comments to try and figure it out, you said: "To get the prior year, 2020 - 2 years by month should I then be using the Year formula you gave provided to get this data?"
What does "the prior year 2020 - 2 years by month" look like? I need an example to understand what you're trying to do.
Mynda
I worked it out and am extremely impressed with myself! This is what I did:
Sales ($) PY:=CALCULATE(SUM(SP_BI_InventorySalesTransactionView[SalesValue]),DATEADD(DateDimensions[TxDate],-2,YEAR))
They want to see 3 years of history and each month the reporting months change. ie right now it would be June 2017 to May 2020 and then in July it would be July 2017 to June 2020.
I had 3 years of data and could get the months to line up but couldn't get the years to line up.
I think I have finally worked it out - probably a better way to do what I'm trying to do... They want a very specific layout and no interactivity (which is killing me - I'd love to put some slicers on it) so now I'm debating using a pivot table, Getpivotdata or cube formulas.
Thank you for your help
Thanks
Maree
Congratulations, Maree! I always find I learn a lot more when I figure something out myself, so well done for persevering. Plus, it feels like winning when you finally see the results you want 🙂
If you don't want to see the PivotTable then CUBE formulas might be all you need, but they still need many supporting cells for the filter context. Either way, it'll all be a learning curve.
Mynda
