Custom Visual to calculate the cumulative sum of a measure

[MeasureMan]

Hi,

I have a complex measures for which I would want to calculate cumulative sum over date column grouped by some other column. Preferably visualized in a line chart.

Yes I can do this with DAX by doing a cumulative calculation of the measure, but doing a cumulative sum over many years with a legend column in a line chart can be quite slow. I can also do this in a Python visual, which is much faster because I can make it calculate the cumulative sum more effectively. However, Python visual doensnt allow tooltips and is not the prettiest and not sure its even possible to make very pretty.

So, are there any custom visuals, that for given date column, legend column and measure(think it as a column), would calculate the running total over the date column by this said measure while grouped by legend column? I have tried to find them, but havent find any. Is it even possible?

Many thanks before hand for possible answers.
 

[MeasureMan]

Hi @Anonymous ,


Thank you for your input.

Yes I can do that, but it has slow to very slow performance. Why? Because it does the same calculation over and over. When you do cumulative sum over DimDate[date] with a much more complicated measure, it becomes a bigger problem. 

A better measure would in fact be:

Cumulative Sum = 
VAR maxdate = calculate(max(DimDate[YearMonth]
RETURN
CALCULATE([Sum Sales], REMOVEFILTERS(DimDate),KEEPFILTERS(DimDate[YearMonth]<=maxdate))

Even this isnt good enough for me.  

In Python I can input the date column and measure as a column along with a legend column. In Python I can calculate the running total much more efficiently than with just dax and the report is much faster. But Im hoping there would be a custom visual for this, but apparently not.

[Anonymous]

Hi @MeasureMan ,

I think you can achieve your goal by creating measures.

Here I create a sample a DimDate and a Sales table.

Cumulative Sum = CALCULATE([Sum Sales],FILTER(ALL(DimDate),DimDate[YearMonth]<=MAX(DimDate[YearMonth])))

Result is as below.

Best Regards,
Rico Zhou

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

[GaleMorin]

Thank you sir, you made my day.

Spoiler

Thank you, sir, you made my day. Pay attention, Parents and students, Education and freedom of expression are two fundamental pillars of our society, and this https://www.englishforums.com/blog/freedom-of-speech-impact-on-education/ article on English forums illustrates their complex relationship beautifully. It’s interesting how the sharing of ideas shapes the learning process. The findings here challenge us to think about how promoting an atmosphere of open dialogue can improve education and help students think critically. A great read that left me thinking about the power of language in the classroom.