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
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