Identify Back Date Entry / Deals - Volume Report

[Anonymous]

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 that is allowing back date entry posting manually in system by Operation team. Till the system is developed we have to find manual way.

Point# 2) Initially Disbursed amount is 50k but after 2-3 months this could be 20k in addition to initial amount. We refer these as Partial Disbursed booked deals.

Point# 3) We have found that in some scenarios Loan Start Date is changed due to Partial Disbursed deals.

We have to make a model to overcome the above conditions in Volume Report.

Regards,
Ashish Mehra

[v-lili6-msft]

hi, @Anonymous

Could you share your sample pbix or some data sample and expected output for us. Do mask sensitive data before uploading.

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490 

Best Regards,

Lin

[Anonymous]

Hi, Please find the sample data with require output. https://drive.google.com/file/d/15zyZ8emFWO2qE5_CrlgqS4AbUM6oHdZE/view?usp=sharing Regards, Ashish Mehra

[v-lili6-msft]

hi, @Anonymous

Whether data from July File and Aug File will be in the same table?

and what is the logic of "Back Date Entry "?

Best Regards,

Lin

[Anonymous]

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

[v-lili6-msft]

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

[Anonymous]

Hi, Please find the link of sample data. Regards, Ashish Mehra