Cumulative distinct count/sum and missing values for years

[Anonymous]

Hi everyone,

I need a hand for a cumulative count which returns blank for missing values in a matrix visual.

I have a table for which I have to count the customer who bought something THAT year in THAT region.
The measure I have for the cumulative count is this:

Cumulative Count =
VAR MinYear = MIN(Table[YearSell])
VAR Calcola = CALCULATE(DISTINCTCOUNTNOBLANK(Table[Customer]),Table[YearSell]<=MinYear)
RETURN Calcola

and this is for the cumulative sum:

Cumulative Amount =
VAR MinYear = MIN(Table[YearSell])
VAR Calcola = CALCULATE(SUM(Table[Amount]),Table[YearSell]<=MinYear)
RETURN Calcola

This works except for the year/region for whose I have no sold items.

So if my data is like this:

Customer	YearSell	Amount
Customer1	2016	10€
Customer2	2017	50€
Customer3	2017	70€
Customer4	2017	80€
Customer5	2017	20€
Customer6	2022	10€

Now my the matrix visual looks like this:

Year	CumulativeCount	CumulativeAmount
2016	1	10€
2017	5	230€
2022	6	240€

But it should look like this:

Year	CumulativeCount	CumulativeAmount
2016	1	10€
2017	5	230€
2018	5	230€
2019	5	230€
2020	5	230€
2021	5	230€
2022	6	240€

So basically filling the blank years with values for the year before, then continuing the cumulative count.

I know the problem is that for some years(2018-2021) I altogether do not have a value, so this is why the measure does not work.
I created another table containing ALL the years and created the relationship, but now I do not know how to write the correct measure.

Does someone know how?

Thanks in advance to everyone!

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[v-yueyunzh-msft · Accepted solution]

Hi , @Anonymous 

Thanks for your sample data you provided. According to your description, you want to "Cumulative distinct count/sum and missing values for years".

Here are the steps you can refer to :
(1)My test data is the same as yours.

(2)We can click "New Table" and enter to create a dimension  table:

Year = GENERATESERIES( MIN('Table'[YearSell]) ,MAX('Table'[YearSell]))

(3)Then we can create two measures:

CumulativeCount = var _cur_year = MAX('Year'[Value])
var _t = FILTER(ALLSELECTED('Table') ,'Table'[YearSell]<= _cur_year)
return
COUNTROWS(_t)

CumulativeAmount = var _cur_year = MAX('Year'[Value])
var _t = FILTER(ALLSELECTED('Table') ,'Table'[YearSell]<= _cur_year)
return
SUMX(_t,[Amount])

(4)Then we can put the fields we need on the visual and we can meet your need:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly