Solved: Cumulative sum by month with missing months result... - Page 2

Anonymous · ‎07-23-2017

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?

Anonymous · ‎07-24-2017

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