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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
LangeJan
Helper I
Helper I

Total count in a period

Hi
I would like to count the total number of customers, so I can show that i 2019 we had 6 customers and in 2020 we had 7 customers and so on. Which function is the right one to choose for that?

I dosnt work for me to use DISTINCTCOUNT or TOTALYTD

LangeJan_0-1669805143729.pngLangeJan_1-1669805196994.png


My data looks like this.

CustNumberCompanyNameCreatedDate
98Very Old Company01-01-1983
99Older Company01-01-1993
100Old Company01-01-2003
101Walmart Inc. (WMT)01-08-2019
102China Petroleum & Chemical Corp. (SNP)01-09-2019
103Amazon.com Inc. (AMZN)01-10-2019
104PetroChina Co. Ltd. (PTR)01-04-2020
105Apple Inc. (AAPL)01-02-2021
106CVS Health Corp. (CVS)02-02-2021
107Royal Dutch Shell PLC (RDS. A)01-05-2022
108Berkshire Hathaway Inc. (BRK. A)01-06-2022
109Toyota Motor Corp. (TM)01-07-2022
110Volkswagen AG (VWAGY)01-08-2022

 

What I would like to show is:

YearTotal Number of Customers CreatedNet increase compared to last year
20196 
202071
202192
2022134

 

 

Thank you

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

Hi @LangeJan ,

 

Create the following two measures:

 

Cumulative customers = CALCULATE(DISTINCTCOUNT('Table'[CustNumber]), 'Table'[year]<= MAX('Table'[year]))

var cumulative =
[Cumulative customers]
    - CALCULATE ( [Cumulative customers], 'Table'[year] = MAX ( 'Table'[year] ) - 1 )

 

MFelix_0-1669893382787.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

2 REPLIES 2
LangeJan
Helper I
Helper I

@MFelix Thanks a lot. Your reply solved my problem.

MFelix
Super User
Super User

Hi @LangeJan ,

 

Create the following two measures:

 

Cumulative customers = CALCULATE(DISTINCTCOUNT('Table'[CustNumber]), 'Table'[year]<= MAX('Table'[year]))

var cumulative =
[Cumulative customers]
    - CALCULATE ( [Cumulative customers], 'Table'[year] = MAX ( 'Table'[year] ) - 1 )

 

MFelix_0-1669893382787.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.