Calculate Distinct Customers by Year based on Start and End Dates

[aellison]

I'm trying to find the correct expression to return DISTINCT counts of customers by Fiscal Year. Blank end dates means that they are current customers. Customers can be in multiple programs at one time, but should still only be counted as a single distinct customer. Here is an example of the dataset:

CustomerID	ProgramID	StartDate	EndDate	Fiscal Year Start	Fiscal Year End
111	1	1/1/2013		2013
222	1	1/1/2014	7/1/2014	2014	2015
333	1	1/1/2016		2016
444	1	6/1/2014	12/31/2015	2014	2015
333	2	2/1/2016	5/1/2016	2016	2016

Here is the output that is needed:

Fiscal Year	# of Customers
2013	1
2014	3
2015	3
2016	2

[OwenAuger · Accepted solution]

Hi @aellison,

You could use an 'events in progress' measure.

Here is a sample PBIX file, using your sample data, with 3 different definitions of the measure:

https://www.dropbox.com/s/m7idfw64eborw5w/Distinct%20Customers%20in%20Program.pbix?dl=1

Note: From your sample data it looked like you have a July-June fiscal year, so I got 3 distinct customers in Fiscal Year 2016 (since 444 extends into FY 2016).

Here is one of the measures:
(note, the measure assumes you might have a relationship between CustomerProgram and Calendar tables):

Distinct Customers in Program  v3 = 
CALCULATE (
    CALCULATE ( DISTINCTCOUNT ( CustomerProgram[CustomerID] ), ALL ( 'Calendar' ) ),
    GENERATE (
        CALCULATETABLE (
            SUMMARIZE (
                CustomerProgram,
                CustomerProgram[CustomerID],
                CustomerProgram[StartDate],
                CustomerProgram[EndDate]
            ),
            ALL ( 'Calendar' )
        ),
        INTERSECT (
            DATESBETWEEN (
                'Calendar'[Date],
                CustomerProgram[StartDate],
                CustomerProgram[EndDate]
            ),
            VALUES ( 'Calendar'[Date] )
        )
    )
)

The measure is based on DAX code from this paper (page 27):

http://www.sqlbi.com/wp-content/uploads/DAX-Query-Plans.pdf

Regards,

Owen 🙂

[ankitpatira]

@aellison Under Modelling tab, click New Table and create as below,

=SUMMARIZE(tableName, tableName[Fiscal Year Start],"a",DISTINCTCOUNT(tableName[CustomerID]) )

[aellison]

Hi @ankitpatira,

Thank you for the quick response. I am performing this within an SSAS Tabular Model ... is your suggestion for Excel only?