KyleHansen
New Member
- Joined
- Nov 17, 2015
- Messages
- 5
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!
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!