cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wdean Regular Visitor
Regular Visitor

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

Accepted Solutions
Highlighted
ValubiMartin Senior Member
Senior Member

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

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

 

 

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

Cumulative total final solution.png

 

12 REPLIES 12
ValubiMartin Senior Member
Senior Member

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

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
Senior Member

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

Hi @wdean

 

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

wdean Regular Visitor
Regular Visitor

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

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
Senior Member

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

@wdean

 

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
Regular Visitor

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

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
Regular Visitor

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

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
Senior Member

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

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

 

 

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

Cumulative total final solution.png

 

wdean Regular Visitor
Regular Visitor

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

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?

 

5.png

 

 

 

 

 

 

ValubiMartin Senior Member
Senior Member

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

@wdean

 

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.