Forum Discussion

PriyankaA905's avatar
PriyankaA905
Frequent Visitor
4 years ago

Lag Report

Hi,    I have to create a lag repot in below format In this case, Value 14,241 no of accounts were opened as on 30 April 18, and are active in April 2018. 14016 no of accounts were opened a...
Need Help
Tips and Tricks
PriyankaA905's avatar
PriyankaA905
Frequent Visitor
4 years ago

Please refer to below image for better understanding

Column namemeaning
ACNTS_BRN_CODEBranch code for a specific account
ACNTS_CLIENT_NUMcustomer number
IACLINK_ACTUAL_ACNUMCustomer's account number.
ACNTS_PROD_CODEProduct code with respect to the transaction 
ACNTS_AC_TYPEAccounts type code
ACNTS_OPENING_DATEAcoount opening date
ACNTS_CLOSURE_DATEAccount closing date
AS_ON_DATEMonth end date
CURRENT_BALANCE_ASONBalance as on last date of month
v-janeyg-msft's avatar
v-janeyg-msft
Community Support
4 years ago

PriyankaA905 

 

The rows and columns in this matrix correspond to which date column you have, and what logic is used to calculate the values in the matrix, and which column number is calculated, you have to make it clear.

Please do not take screenshots, share sample data in table format or link, otherwise we can't replicate the test data.

Thank you.

 

Best Regards,
Community Support Team _ Janey

 

  • PriyankaA905's avatar
    PriyankaA905
    Frequent Visitor
    4 years ago

    April 2018, March 2018,... we have made duplicate column of AS_ON_DATE with Month and year in it

    30 April 2018, 31 May 2018, ... is a AS_ON_DATE column

    We have used distinct values of IACLINK_ACTUAL_ACNUM for values

     

    ACNTS_BRN_CODEACNTS_CLIENT_NUMIACLINK_ACTUAL_ACNUMACNTS_PROD_CODEACNTS_AC_TYPEACNTS_OPENING_DATEACNTS_CLOSURE_DATEAS_ON_DATECURRENT_BALANCE_ASON
    5111222001010010100014501101552016/21/2017 12:00:00 AMnull2/28/2021 12:00:00 AM40
    5112222001110010100014501112552026/21/2017 12:00:00 AMnull2/28/2021 12:00:00 AM10800
    51132220012100101000145011235050112/24/2020 12:00:00 AMnull2/28/2021 12:00:00 AM1250
    51142220013100101000145011335050112/24/2020 12:00:00 AM12/24/2020 12:00:00 AM2/28/2021 12:00:00 AM100
    51152220014100101000145011435050112/24/2020 12:00:00 AMnull2/28/2021 12:00:00 AM100
    51162220015100101000145011535050112/24/2020 12:00:00 AMnull2/28/2021 12:00:00 AM5350
    51172220016100101000145011635050112/24/2020 12:00:00 AMnull2/28/2021 12:00:00 AM100
    51182220017100101000145011735050112/24/2020 12:00:00 AMnull2/28/2021 12:00:00 AM3800
    51192220018100101000145011835050112/24/2020 12:00:00 AMnull2/28/2021 12:00:00 AM3800
    51202220019100101000145011935050112/24/2020 12:00:00 AMnull3/31/2021 12:00:00 AM100
    51212220020100101000145012035050112/24/2020 12:00:00 AMnull3/31/2021 12:00:00 AM5700
    51222220021100101000145012135050112/24/2020 12:00:00 AMnull3/31/2021 12:00:00 AM100
    51232220022100101000145012235050112/24/2020 12:00:00 AMnull3/31/2021 12:00:00 AM100
    51242220023100101000145012335050112/24/2020 12:00:00 AMnull3/31/2021 12:00:00 AM1250
    51252220024100101000145012435050112/24/2020 12:00:00 AM10/3/2021 0:003/31/2021 12:00:00 AM8900
    51262220025100101000145012535050112/28/2020 12:00:00 AMnull3/31/2021 12:00:00 AM3050
    51272220026100101000145012635050112/28/2020 12:00:00 AMnull3/31/2021 12:00:00 AM6200
    51282220027100101000145012735050112/28/2020 12:00:00 AMnull3/31/2021 12:00:00 AM2
    51292220028100101000145012835050112/28/2020 12:00:00 AMnull3/31/2021 12:00:00 AM390
    51302220029100101000145012935050112/28/2020 12:00:00 AMnull3/31/2021 12:00:00 AM6200
    5111222001010010100014501101552016/21/2017 12:00:00 AMnull4/30/2021 12:00:00 AM40
    5112222001110010100014501112552026/21/2017 12:00:00 AMnull4/30/2021 12:00:00 AM10800
    51132220012100101000145011235050112/24/2020 12:00:00 AMnull4/30/2021 12:00:00 AM1250
    51152220014100101000145011435050112/24/2020 12:00:00 AMnull4/30/2021 12:00:00 AM100
    51162220015100101000145011535050112/24/2020 12:00:00 AMnull4/30/2021 12:00:00 AM5350
    51172220016100101000145011635050112/24/2020 12:00:00 AMnull4/30/2021 12:00:00 AM100
    51182220017100101000145011735050112/24/2020 12:00:00 AMnull4/30/2021 12:00:00 AM3800
    51192220018100101000145011835050112/24/2020 12:00:00 AMnull4/30/2021 12:00:00 AM3800
  • v-janeyg-msft's avatar
    v-janeyg-msft
    Community Support
    4 years ago

    Hi, PriyankaA905 

     

    According to your description,I think you need to ceate a single column table as martix column and then create the measure to display count.

     

    Table 2 = DISTINCT('Table'[AS_ON_DATE])
    measure =
COUNTX (
    CALCULATETABLE (
        DISTINCT ( TABLE[IACLINK_ACTUAL_ACNUM] ),
        FILTER (
            ALL ( 'Table' ),
            [ACNTS_OPENING_DATE] >= SELECTEDVALUE ( 'Table'[AS_ON_DATE] )
                && [ACNTS_CLOSURE_DATE] <= SELECTEDVALUE ( 'Table 2'[AS_ON_DATE] )
        )
    ),
    [IACLINK_ACTUAL_ACNUM]
)

     

    Did I answer your question? Please mark my reply as solution. Thank you very much.
    If not, please feel free to ask me.

    Best Regards,
    Community Support Team _ Janey