Forum Discussion
Identify Back Date Entry / Deals - Volume Report
Hi, We are facing 3 issues as of now: 1) Back Date Entry posted in system 2) Change in Loan Amount 3) Change in Loan Start Date column Explaination: Point# 1) There is a bug in our system ...
Hi
Jul & Aug file are two tables.
Logic of Back date entry / deal is :
First; in our sample file in Data worksheet we have two tables Volume Report till July & Aug. Let's name them Volume Report July as "Table 1" & other as "Table 2"
e.g. we have 3 deals booked in Apr refer Table 1.
Ops team manually booked deals of previous month's in the month of April & now we have 4 deals booked in the month of April refer Table 2
The logic to calculate Volume Report is Disbursed amount is freeze.
There can not be any (+) or (-) for previous month (s)
Let me share the process we follow:
Ops team shared mail for any back date entry they manually post in the system.
With their help we figure out number & amount by which the back date deals are posted in the system.
Here we show impact ( Disbursed amount) in the current month (Table2).
We can not rely on Ops & would like to ensure we do not miss any entry.
You may refer to Ouput worksheet for more understanding.
Write back for any clarification.
Regards
Ashish Mehra
Jul & Aug file are two tables.
Logic of Back date entry / deal is :
First; in our sample file in Data worksheet we have two tables Volume Report till July & Aug. Let's name them Volume Report July as "Table 1" & other as "Table 2"
e.g. we have 3 deals booked in Apr refer Table 1.
Ops team manually booked deals of previous month's in the month of April & now we have 4 deals booked in the month of April refer Table 2
The logic to calculate Volume Report is Disbursed amount is freeze.
There can not be any (+) or (-) for previous month (s)
Let me share the process we follow:
Ops team shared mail for any back date entry they manually post in the system.
With their help we figure out number & amount by which the back date deals are posted in the system.
Here we show impact ( Disbursed amount) in the current month (Table2).
We can not rely on Ops & would like to ensure we do not miss any entry.
You may refer to Ouput worksheet for more understanding.
Write back for any clarification.
Regards
Ashish Mehra
hi, Anonymous
Is this the case exsit? In Aug File, there are other month data, e.g. AC019 date is 1-Oct-18. If so, what is the date of AC015?
| AC015 | 1-Apr-18 | Personal Loan | 20 | Back Date Entry (The amount will be counted in the Aug Volume Report) |
And for your sample data, you could try this way as below:
Step1:
Use LOOKUPVALUE Function to add the the data from Jul table
Jul date = LOOKUPVALUE(Jul[Date],Jul[LAN],Aug[LAN])
Jul Amount = LOOKUPVALUE(Jul[Amount],Jul[LAN],Aug[LAN])
Step2:
Use the logic create new date and Amount column for Aug table like below:
New date = var _a=IF(ISBLANK(Aug[Jul date])&&NOT(Aug[Date]) in VALUES(Aug[Jul date]),Aug[Date],Aug[Jul date]) return IF(ISBLANK(_a),MAX(Aug[Date]),_a)
New amount = var _differece=CALCULATE(SUM(Aug[Amount]),FILTER(Aug,ISBLANK(Aug[Jul date])=FALSE()))-CALCULATE(SUM(Aug[Jul Amount]),FILTER(Aug,ISBLANK(Aug[Jul date])=FALSE())) return var _addrows=CALCULATE(COUNTROWS(Aug),FILTER(Aug,ISBLANK(Aug[Jul date]))) return IF(ISBLANK(Aug[Jul date]),Aug[Amount]+_differece/_addrows,Aug[Jul Amount])
Result:
here is pbix file, please try it.
Best Regards,
Lin