Working with date ranges to find initial install date count

Question

Good Afternoon,&nbsp;I was trying to find a way to pull numbers using a date range.&nbsp; We are wanting to check when a service was initially installed and have that show as an initial install count.&nbsp; &nbsp;&nbsp;Address IDAddressInstall DateDisconnect Date1123 Sample Dr4/20/20189/18/20181123 Sample Dr4/20/20189/18/20181123 Sample Dr4/25/20182/23/20191123 Sample Dr5/1/2019&nbsp;&nbsp;In the example above I have a single address that we are wanting to show as an inital install count of 2 (1 on 4/20/2018 and 1 on 5/1/2019).&nbsp;&nbsp;&nbsp;The service in the example was initially installed on 4/20/2018 (two services but we only want to count as a single initial install).&nbsp; They added another service five days later.&nbsp; So we would want to show on our report a initial install date of 4/20/2018 and ignore the 4/25/2018 since it was installed while the inital services were still active.&nbsp;&nbsp;&nbsp;My issue comes up whenever a customer disconnects all services.&nbsp; In the example above the customer had at least one service active from 4/20/2018 to 2/23/2019.&nbsp; Then they had a break in service until 5/1/2019.&nbsp;&nbsp;&nbsp;We would want the 5/1/2019 to show as an inital count of 1 for that date.&nbsp;&nbsp;&nbsp;Can someone help me figure out how I would need to accomplish this or if it is even possible.&nbsp; On things like these we usually only care about the very first time a person got service and I would go to power query short the table based on install date then buffer the table then remove duplicates.&nbsp; However in this scenario we would want to know of any breaks in service and count anything after that break as a new count.&nbsp;&nbsp;

v-cherch-msft · Accepted Answer

Hi&nbsp;mattyj2009&nbsp;
You may add index first and then create columns and measures as below.Attached sample file for your reference.
Last_DisconnectDate = 
VAR last_Dis_date =
    CALCULATE (
        MAX ( Data[Disconnect Date] ),
        FILTER (
            ALLEXCEPT ( Data, Data[Address ID] ),
            Data[Index]
                = EARLIER ( Data[Index] ) - 1
        )
    )
RETURN
    IF ( last_Dis_date = BLANK (), Data[Disconnect Date], last_Dis_date )

New_index = IF(Data[Install Date]&gt;Data[Last_DisconnectDate],Data[Index],0)
Count = 
VAR min_date =
    CALCULATE (
        MIN ( Data[Install Date] ),
        FILTER (
            ALL ( Data ),
            Data[Address ID] = MAX ( Data[Address ID] )
                &amp;&amp; Data[New_index] = MAX ( Data[New_index] )
        )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Data[Address ID] ),
        FILTER ( Data, IF ( Data[Install Date] = min_date, 1 ) )
    )

CountSum = SUMX(VALUES(Data[Install Date]),[Count])
Regards,

Forum Discussion

Working with date ranges to find initial install date count

2 Replies

Related content

SQL initialization error

Tesseract install

Airflow Scheduler not working when installing Astronomer Cosmos and dbt Fabric

Power BI Gateway Installation issues

Incremental Refresh - DateTime Partition Column Works in DEV but Initial Refresh Times Out in PROD

Address ID	Address	Install Date	Disconnect Date
1	123 Sample Dr	4/20/2018	9/18/2018
1	123 Sample Dr	4/20/2018	9/18/2018
1	123 Sample Dr	4/25/2018	2/23/2019
1	123 Sample Dr	5/1/2019