Life to Date and Year over Year on one matrix

[Kalachuchi]

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

CustomerNumberCount = DISTINCTCOUNT(Report[CustomerNumber])

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.

[v-yetao1-msft · Accepted solution]

Hi @Kalachuchi 

Create a column like this :

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

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]

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