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
Anonymous
Not applicable

Cumulative sum by month with missing months results in gaps in clustered column chart

I am tring to have a chart the shows cumulative sum within a financial year including any missing months. The problem is I am extracting data from sql server 2008 ( I have tried to fix in sql but found it to complicated) and many months have no data as can be seen below in a matrix.

 

(so ideally oct-2016 should be 6,450.00)

 

2.png

 

I am trying to make a culmative clustered column chart like this.

 

3.png

but my data has missing months, so in power bi it ends up looking like this with missing columns:

1.png

 

The example I have shown is just one project, I have thousands of projects spanning over multiple financial years. 

 

data looks like this:

4.png

Any help would be much appreciated.

 

If needed I can upload my pbix, im just not sure how to do it?

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @Anonymous

 

So I've created a new example with equivalent data to what you have. Sorry, I missed that there were no records for months without any values.

 

I'm using 2 tables to solve the problem. The Value-data and a time dimension. You'll create the time dimension by going to "Modeling" in the menu, then click "New Table". This will send you to a dax formula, where you should write the following:

 

TimeDimCumulative = CALENDAR(FIRSTDATE('Cumulative Total'[Dato]);LASTDATE('Cumulative Total'[Dato]))

 

you'll see pictures of my data in the bottom of my reply. 

 

So the DAX formula for the cumulative sum is slightly different now, since we are now using the time dimension. It goes like this:

 

Cumulative total = CALCULATE(SUM('Cumulative Total'[Value]);FILTER(ALL('Cumulative Total'[Dato]); 'Cumulative Total'[Dato] <= MAX ( TimeDimCumulative[Date] )))

 

Having dates from the time dim plotted into a table and writing the DAX formula this way, the table will show values for the missing dates. See the last picture for the solution.

 

I hope this solves your problem. Let me know, if I need to elaborate on anything, or if I misunderstood your challenge. Then we'll work together to solve it.

 

Best,

Martin

 

 

Cumulative total - Data.pngCumulative total - time dim.png

Cumulative total final solution.png

 

View solution in original post

12 REPLIES 12
Anonymous
Not applicable

Hi @Anonymous

 

I'm just going to give you this DAX calculation. It's a measure:

 

Cumulative total of value = CALCULATE (
SUM ( Test[Value] );
FILTER (
ALL ( Test[Dato] );
Test[Dato] <= MAX ( Test[Dato] )
)
)

 

I got inspired by this article.

 

Hope this solves your problem. See picture below.

 

Best,

Martin

 

Cumulative total.png

Anonymous
Not applicable

Hi @Anonymous

 

I have a solution in mind, I just want to know one thing, before I give it to you. Will the value always be missing for only one month or is it possible that it will miss for several months in a row?

 

Best,

Martin

Anonymous
Not applicable

Hi. It is possible thay many are missing in a row. Even only one value for the whole fiscal year but I still need to see each month in the chart
Anonymous
Not applicable

@Anonymous

 

If you tried my DAX formula, you should be able to see the whole year, even though all months are missing except one. Alternatively, you can make a new column, where you insert 0 if there is no value for that month.

 

Let me know, if this solves your problem. I'll gladly elaborate on anything.

Anonymous
Not applicable

Thanks @Anonymous for your responces.

 

The difference between my example and yours is that I assume you have feb as blank, I dont even have a row in feb, therefore it will not be included.

Anonymous
Not applicable

My question appears to be the same as:

https://community.powerbi.com/t5/Desktop/Add-rows-missing-date-values/td-p/118463

 

I have thousands of different catergories, so unfortunately there is no easy way to attone for the missing values.

 

I might have to try to do in sql?

Anonymous
Not applicable

Hi @Anonymous

 

So I've created a new example with equivalent data to what you have. Sorry, I missed that there were no records for months without any values.

 

I'm using 2 tables to solve the problem. The Value-data and a time dimension. You'll create the time dimension by going to "Modeling" in the menu, then click "New Table". This will send you to a dax formula, where you should write the following:

 

TimeDimCumulative = CALENDAR(FIRSTDATE('Cumulative Total'[Dato]);LASTDATE('Cumulative Total'[Dato]))

 

you'll see pictures of my data in the bottom of my reply. 

 

So the DAX formula for the cumulative sum is slightly different now, since we are now using the time dimension. It goes like this:

 

Cumulative total = CALCULATE(SUM('Cumulative Total'[Value]);FILTER(ALL('Cumulative Total'[Dato]); 'Cumulative Total'[Dato] <= MAX ( TimeDimCumulative[Date] )))

 

Having dates from the time dim plotted into a table and writing the DAX formula this way, the table will show values for the missing dates. See the last picture for the solution.

 

I hope this solves your problem. Let me know, if I need to elaborate on anything, or if I misunderstood your challenge. Then we'll work together to solve it.

 

Best,

Martin

 

 

Cumulative total - Data.pngCumulative total - time dim.png

Cumulative total final solution.png

 

Anonymous
Not applicable

Hi @Anonymous,

 

You have understood my problem exactly correct thankyou for your efforts.

 

But I can not get your solution to work.

 

It doesnt show the missing months or cumulate by month.

 

The only difference is it would not let me use ; so I used , instead?

 

Would you be able to send me the pbix so I can copy your method exactly?

 

5.png

 

 

 

 

 

 

Anonymous
Not applicable

@Anonymous

 

There is no difference between using ";" and ",".

 

I'm glad that I understand your problem. I'm not actually sure how to attache pbix., but here is a dropboxlink to the file, so you can copy my solution.

 

Let me know how it goes. I'll gladly elaborate and help you implement this, should you have any issues.

Anonymous
Not applicable

@Anonymous

 

How did it go? I just want to follow up - could you replicate my work?

 

Best,

Martin

Anonymous
Not applicable

Hi @Anonymous. Appreciate all your efforts. I could not get your soultion to work. Even used your pbix. I think the issue was I did not have a date and artifically created one. I worked around the issue by using sql server. Much thanks for you effort.

 

 

I also faced the same problem. I was finding it difficult to follow the advice given from several locations to produce the desired result. However, by downloading your pbi file, I was able to play with the data in such a way to educate myself. I was able to create the desired result (a stacked area chart) with my data. I'm sure others will be able to do the same.CummlativeExample - Power BI Desktop.jpg

 

 

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.