- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Welcome to the Community
- Community Feedback
- Community Help Blog
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: Cumulative sum by month with missing months re...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

wdean

Regular Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-23-2017
11:48 PM

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)

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

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

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

data looks like this:

Any help would be much appreciated.

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

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

ValubiMartin

Senior Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-24-2017
11:38 PM

Hi @wdean

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

12 REPLIES 12

ValubiMartin

Senior Member

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-24-2017
01:30 AM

Hi @wdean

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

ValubiMartin

Senior Member

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-24-2017
02:22 AM

wdean

Regular Visitor

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-24-2017
05:50 AM

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

ValubiMartin

Senior Member

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-24-2017
06:07 AM

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.

wdean

Regular Visitor

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-24-2017
05:11 PM

Thanks @ValubiMartin 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.

wdean

Regular Visitor

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-24-2017
05:24 PM

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?

Highlighted

ValubiMartin

Senior Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-24-2017
11:38 PM

Hi @wdean

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

wdean

Regular Visitor

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-25-2017
09:07 PM

Hi @ValubiMartin,

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?

ValubiMartin

Senior Member

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-25-2017
11:32 PM

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.