Forum Discussion
Impactful IndividualStruggling with forecasting forward
I have a set of cusotmer data that I am trying ot profile the debt on looking forward. I know what the current debt is of each customer currently and also how long ago they stopepd being customers. e.g.
| Customer No | Months Left | Current Debt |
| 1 | 3 | £ 50.00 |
| 2 | 5 | £ 20.00 |
| 3 | 2 | £ 80.00 |
| 4 | 7 | £ 150.00 |
By doing some historicla analyis I've come up with a typical profile of debt form the moment the average cusotmer leaves us that looks something like
| Months After leaving | Av Change in Debt |
| 1 | 20 |
| 2 | 10 |
| 3 | 5 |
| 4 | 0 |
| 5 | -5 |
| 6 | -10 |
| 7 | -15 |
| 8 | -20 |
| 9 | -30 |
| 10 | -10 |
The output i need is a forecast going ofrward by month of how the total debt is expected to evolve. So woudl look a pivot table like this (down the side will be custoemr types etc)
| Forecast | ||
| 0 | 1 | 2 |
| 300 | 275 | 225 |
The calaculation is
previous balance+ for each cusotmer look up the change in debt for x months after they left us e.g. for cusotmer 1 the expected change in the next month is 3+1 =4 = 0, for 2 its 5+1=6=-10, 3 is 2+1=3=5, 4 is 7+1=8=-20 total -25 so new balance is 275
I've been scrathcing my head for days and can't even think how to get started on a measure that will go in the pivot table vlaues that will pick up the number months fowd, apply ito every customer calcauting that cusotmers expected change then sum it up.
The debt profile is something like a disconnected slicer I guess?
Appreciate any tips on how to move forward
Mike
I actually have differnet classifacations for cusotmers and seperate debt porfiles for each cusotmer type.
Hi lin
I'm almost there using your idea of the VAR to feed in the months.
Fwd Bal Ch:=VAR FwdMonths=MIN(DateTable[Forecast Month]) RETURN SUMX( Customers_2018_Sep_06, SUMX( FILTER( Forecast, Customers_2018_Sep_06[Move Out Tariff]=Forecast[Move Out Tariff] && Customers_2018_Sep_06[Move Out Status]=Forecast[Move Out Status] && Customers_2018_Sep_06[Move out Band]=Forecast[Move Out Band] && Forecast[Months After]=customers_2018_Sep_06[Months since Move]+FwdMonths ), Forecast[Bal Ch] ) )This code calculates the change for each customer and aligns it with the forecast month as below. Only issue is the grand total is the same as column 1 because I used MIN in the VAR so just plugs 1 in when there is no context coming from the columns. I've tried all sorts of things to get the grand totla to be the sum of columns, but can't seem to crack it. Any suggestions woudl be great.
Mike
10 Replies
v-lili6-msftCommunity Support
hi,@masplin
After my research, you can do these follow my steps as below:
Step1:
Use crossjoin function to create a table
Table = CROSSJOIN(Table1,Table2)
Step1;
add a Months After leaving list table
Step3:
Add these measure in the Table by crossjoin
Measure = Var _mons=MIN(Table3[Mons]) RETURN VAR _debt=CALCULATE(SUM('Table'[Av Change in Debt]),FILTER('Table','Table'[Months After leaving]='Table'[Months Left]+_mons)) return _debtMeasure 2 = SUMX('Table3',[Measure])Forecast = SUM(Table1[Current Debt])+CALCULATE([Measure 2],FILTER(ALL(Table3),Table3[Mons]<=MAX(Table3[Mons])))
Step4:
Drag fields into table visual
here is pbix, please try it.
https://www.dropbox.com/s/npkbycwofrmzmlr/Struggling%20with%20forecasting%20forward.pbix?dl=0
Best Regards,
Lin
masplinSo essentially crossjoin creates multiple versions of my cusotmer for every entry in the debt forecast table. I have 1mio customers and 36 months so my cross join will be 36mio rows I assume!!! Let's see if it blows up!!!!
Thanks for suggestion
Mike
- masplin
My data is actually in excel as I still find excel powerpivot much better when building these complex models. I guess there is no way to do a cross join between tables in the powerpivot datamodel? I'm going to try your idea by importing into desktop, but meanwhile I made some progress on my own with the right numbers but not in the right columns. I was wondering if you can see a tweak?
Customers = Table1 and Forecast = Table2 with [Bal Ch]=[Av Ch in Debt]. This generates the right numbers but aligned to the month after they left instead of the Monthsleft i.e if someone left 3 months ago it calcualtes correctly the next month, which is month 4 for that cusotmer, but i have to use the months after leaving as the column header.
Est Bal Ch:=SUMX( Customers_2018_Sep_06, SUMX( FILTER( Forecast, Forecast[Months After]>customers_2018_Sep_06[Months since Move] ), Forecast[Bal Ch] ) )So the output looks like this, but need to be able to use the Mons across the top so all the next month changes line up. i tried using a variable a bit liek you did to pick off the forecast month ({Mon] in your case) and feed it into the formula, but just generated a single number in all cells Feel sure i'm not that far off?.
Fwd Bal Ch:=VAR FwdMonths=MAX(DateTable[Forecast Month]) RETURN SUMX( Customers_2018_Sep_06, SUMX( FILTER( Forecast, Customers_2018_Sep_06[Months since Move]+FwdMonths>customers_2018_Sep_06[Months since Move] ), Forecast[Bal Ch] ) )
- masplin
Hi Lin
I'm stuck on step 1!!! How do you create a table using CROSSJOIN?
Mike
- v-lili6-msft
hi,@masplin
I have uploaded my demo pbix file, and what is trouble when create a table using CROSSJOIN?
Could you describe it more clearly by screenshots?
It is better that you can share your sample pbix. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.
Best Regards,
Lin