Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mattyj2009
Helper II
Helper II

Working with date ranges to find initial install date count

Good Afternoon,

 

I was trying to find a way to pull numbers using a date range.  We are wanting to check when a service was initially installed and have that show as an initial install count.   

 

Address IDAddressInstall DateDisconnect Date
1123 Sample Dr4/20/20189/18/2018
1123 Sample Dr4/20/20189/18/2018
1123 Sample Dr4/25/20182/23/2019
1123 Sample Dr5/1/2019 

 

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

 

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).  They added another service five days later.  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.  

 

My issue comes up whenever a customer disconnects all services.  In the example above the customer had at least one service active from 4/20/2018 to 2/23/2019.  Then they had a break in service until 5/1/2019.  

 

We would want the 5/1/2019 to show as an inital count of 1 for that date.  

 

Can someone help me figure out how I would need to accomplish this or if it is even possible.  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.  However in this scenario we would want to know of any breaks in service and count anything after that break as a new count.  

1 ACCEPTED SOLUTION
v-cherch-msft
Employee
Employee

Hi @mattyj2009 

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]>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] )
                && 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,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-cherch-msft
Employee
Employee

Hi @mattyj2009 

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]>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] )
                && 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,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

This is exactly what I needed.  I really appreicate your help.  Have a great day!!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.