Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
PriyankaA905
Frequent Visitor

Lag Report

‎04-14-2022 01:18 AM

Hi, 

 

I have to create a lag repot in below format

PriyankaA905_0-1649923962852.png

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 as on 30 April 18 but only this account are active in May 31 18 and so on

Likewise

18,116 no of accout were opened as on 31 May 2018, and successive number "17,818","17623"...are active in following months.

 

Which dax can be used to calculate 

Labels:
Message 1 of 11
717 Views
0
10 REPLIES 10
PriyankaA905
Frequent Visitor

‎04-14-2022 02:40 AM

The data is related to bank. We have below columns in one table

Column nameDescription
ACNTS_CLIENT_NUMClient number
IACLINK_ACTUAL_ACNUMActual Account number
ACNTS_OPENING_DATEOpening date
ACNTS_CLOSURE_DATEClosing date
AS_ON_DATEBalances as on date
Message 11 of 11
668 Views
0
amitchandak
Super User
Super User

‎04-14-2022 01:51 AM

@PriyankaA905 ,Do you have open and close date ?

 

What you need is an approach to this blog means two date tables, one for creation/open date and another to know open cases 

Period Of Stay – Cohort Analysis: https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-3-Period-Of-Stay-Cohort-Anal...

 

 

Part of this will come from

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

 

you need 2 date tables not 1 like the HR blog

 

 

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview. ✨
️ November 12th-14th, 2024
 Online Event
Register Here
Message 2 of 11
675 Views
0
PriyankaA905
Frequent Visitor
In response to amitchandak

‎04-14-2022 01:59 AM

We have open and closed dates

Message 3 of 11
674 Views
0
amitchandak
Super User
Super User
In response to PriyankaA905

‎04-14-2022 02:08 AM

@PriyankaA905 , Take two date tables, Use one date table to create a measure like Current Employee (no join needed, you can avoid cross join then)

and another one for the start date (Join with start date)

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview. ✨
️ November 12th-14th, 2024
 Online Event
Register Here
Message 4 of 11
670 Views
0
PriyankaA905
Frequent Visitor
In response to amitchandak

‎04-14-2022 06:11 AM

We dont have separate Date Dim . The date is in same table as open and close data

The data is related to bank. We have below columns in one table

Column nameDescription
ACNTS_CLIENT_NUMClient number
IACLINK_ACTUAL_ACNUMActual Account number
ACNTS_OPENING_DATEOpening date
ACNTS_CLOSURE_DATEClosing date
AS_ON_DATEBalances as on date

 

Message 5 of 11
664 Views
0
v-janeyg-msft
Community Support
Community Support
In response to PriyankaA905

‎04-18-2022 12:16 AM

Hi, @PriyankaA905 

 

At present we don't know the direct connection between your logic and data. Please provide a valid sample, otherwise it will be difficult for us to help you.

You need to read the doc first:

How to Get Your Question Answered Quickly - Microsoft Power BI Community

 

Best Regards,
Community Support Team _ Janey

Message 6 of 11
643 Views
0
PriyankaA905
Frequent Visitor
In response to v-janeyg-msft

‎04-18-2022 03:58 AM

Please refer to below image for better understanding

PriyankaA905_0-1650279369886.png

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
Message 7 of 11
638 Views
0
v-janeyg-msft
Community Support
Community Support
In response to PriyankaA905

‎04-18-2022 04:17 AM

@PriyankaA905 

 

vjaneygmsft_0-1650280414756.png

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

 

Message 8 of 11
634 Views
0
PriyankaA905
Frequent Visitor
In response to v-janeyg-msft

‎04-18-2022 04:25 AM

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
Message 9 of 11
630 Views
0
v-janeyg-msft
Community Support
Community Support
In response to PriyankaA905

‎04-19-2022 11:38 PM

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

Message 10 of 11
625 Views
0

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

View All