Forum Discussion
Line Charts with Cumulative values
Hi GaRaGe,
First, you should create a calculated column to get a number format month. If we use the Jan, Feb, Mar and so on. Month will order by alphabet.Month! = SWITCH(Test[Month],"Jan",1,"Feb",2,"Mar",3,"Apr",4,"May",5,"Jun",6,"Jul",7,"Aug",8,"Sep",9,"Oct",10,"Nov",11,12)
Then create two measure for cumulative spend over month.Target = CALCULATE(SUM(Test[Spend]),FILTER(ALL(Test),Test[Month!]<=MAX(Test[Month!])&&Test[Metric]="Target")) Actual = CALCULATE(SUM(Test[Spend]),FILTER(ALL(Test),Test[Month!]<=MAX(Test[Month!])&&Test[Metric]="Actual"))
Finally, create a line chart, select the [month!] field as axis, the two measures as value level.
Please feel free to ask if you any issue.
Best Regards,
Angelia
Hi,
You should have a relationship (Many to One and Single) between the Month invoiced column and the Date column of the Date Table. Ensure that the Month name column is sorted by the Month number column in the Date table. To the X-axis of your visual, drag Year and Month name from the Date table. Write these measures:
Actual Cumulative = CALCULATE(SUM(InvoiceDetails[Total LAD Funding]),datesbetween('Date'[Date],minx(all('Date'[Date]),'Date'[Date]),max('Date'[Date])))Hope this helps.
Hi there,
No luck im afriad. Chart looks like this
Here's the link to my file https://drive.google.com/file/d/1-litA5nfaKu4OPcpEVEGnKJG3E4sfA8E/view?usp=sharing
Any help is appreciated.
Hi,
Make the first relationship active.
Super. Thanks.
However the chart looks like this
The chart goes go up and seems to be ignoring every other month using continuous.
Or looks like this if categorical shows every day individually.
I'm looking for it to show every month on the x axis so the chart goes up gradually rather than in chunks.