Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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
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
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
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.
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.
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?
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
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?
@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
How did it go? I just want to follow up - could you replicate my work?
Best,
Martin
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.
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |