Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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 🙂
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 🙂
@aellison Under Modelling tab, click New Table and create as below,
=SUMMARIZE(tableName, tableName[Fiscal Year Start],"a",DISTINCTCOUNT(tableName[CustomerID]) )
Hi @ankitpatira,
Thank you for the quick response. I am performing this within an SSAS Tabular Model ... is your suggestion for Excel only?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
83 | |
67 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |