Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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 ...
Need Help
Show and Tell
v-lili6-msft's avatar
v-lili6-msft
Icon for Community Support rankCommunity Support
7 years ago

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's avatar
    Anonymous
    Not applicable
    7 years ago
    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's avatar
      v-lili6-msft
      Icon for Community Support rankCommunity Support
      7 years ago

      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