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
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
v-yetao1-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
v-yetao1-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.

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

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.