cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kalachuchi
Helper III
Helper III

Life to Date and Year over Year on one matrix

Hey Guys I have a matrix which shows the distinctcount of customernumber 

CustomerNumberCount = DISTINCTCOUNT(Report[CustomerNumber])

Kalachuchi_0-1632306064610.png

 

The matrix above shows the distinctcount of customer number throughout the years.

What I want to do is have a column on the last before total to have Life to Date (LTD) when the business has started or just count all the data per country from 2016 to 2022 ( I excluded 2016 since its not needed to show anymore).

I also want to get the percentage between each year to the total population.

1 ACCEPTED SOLUTION
Ailsa-msft
Community Support
Community Support

Hi @Kalachuchi 

Create a column like this :

CustomerNumberCount=CALCULATE(DISTINCTCOUNT(report[customernumber]),FILTER(ALLEXCEPT(report,report[Region]),report[Year]>2016))

Ailsamsft_0-1632795810533.png

I have attached my pbix file ,you can refer to it .

 

Best Regards

Community Support Team _ Ailsa Tao

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
Ailsa-msft
Community Support
Community Support

Hi @Kalachuchi 

Create a column like this :

CustomerNumberCount=CALCULATE(DISTINCTCOUNT(report[customernumber]),FILTER(ALLEXCEPT(report,report[Region]),report[Year]>2016))

Ailsamsft_0-1632795810533.png

I have attached my pbix file ,you can refer to it .

 

Best Regards

Community Support Team _ Ailsa Tao

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

amitchandak
Super User
Super User

@Kalachuchi , Prefer to have a separate year or date table, try like

 

CustomerNumberCount = calculate(DISTINCTCOUNT(Report[CustomerNumber]), filter(all(Table[Year]), Table[Year] >2016))

or

CustomerNumberCount = calculate(DISTINCTCOUNT(Report[CustomerNumber]), filter(all(Table), Table[Year] >2016))
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.