Rolling calculation per account

[KyleHansen]

I'm putting a monthly sales report together that we'll send our sales people, and what my end goal is, is a pivot that list the Salespeople, their Accounts, and then their sales across different periods, something like this for February, and have the report created each month with a rolling calculation in there. It seems like it should be simple aggregation, but.... I can't seem to get the columns to add up.

For reference - I work at a mortgage company so:
Sales = Funded mortgages
Submissions = total incoming deals
Funded = # of submissions that funded

What I need my pivot to look like is:


January Sales | January Submissions | January Funded (Units) | Current YTD $ | Last Year YTD $ | Last Year Total Sales $
Salesperson 1
Account 1
Account 2
Account 3
Salesperson 2
Account 1
Account 2
Account 3


The data table I have looks like this, both DateRecieved and FundedDate are linked to separate date tables.

DealReceivedDate | Account | Salesperson | DealStatus (status' are: Cancelled, Declined, Funded) | MortgageAmount | FundedDate (only shows a date if the mortgage funded)


So in the sales columns, I need it to calculate the total sales in a period, of deals that are funded, and have that date move every month.

I've got the following measures:

Total Sales
Last Year Sales

For Previous Year YTD, I tried: =calculate([Total Funded], SAMEPERIODLASTYEAR(datesytd(dimFundedDate[Calendar Date])))
, but... I know I'm missing something in there.

For Previous month, I tried PREVIOUSMONTH but returned nothing as well..


Unfortunately I can't share the workbook because of the information in it, but can provide general data... if anyone can help it would be much appreciated! I've been stuck on this all week!

 

[Matt Allington]

Try breaking the problem into manageable pieces.
First create YTD this year that works before you move to last year.

YTD=CALCULATE([Total Sales],datesytd(calendar[date]))

put it it in a pivot with months on rows and total sales in values and a filter for a single year. Then add the new YTD and check it works. Once it is working, then do the time shift for YTD LY

YTD LY = CALCULATE([YTD], sameperiodlastyear(calendar[date]))

you you may like to consider reading my book - it covers this topic as well as many others you will come across in an easy to learn framework. Http://xbi.com.au/learndax

 

[KyleHansen]

Hey Matt, thanks for the reply - I'm way ahead of you and have picked up both yours and Rob Collies books (both have supercharged me from where I was at).

I have tried those calculations and they haven't worked - so my assumption is I'm doing something wrong along the way. I'll give it another shot and may end up reaching out for some consulting work as I have a deadline I'm working with.

Cheers, Kyle

 

[SimonNU]

Are you able to upload an obfuscated workbook?