Forum Discussion

GaRaGe's avatar
GaRaGe
New Member
9 years ago
Solved

Line Charts with Cumulative values

 I have my data like this (image below)   Now i need to create a line chart in Power BI. I am getting something like this (sample image below)     Just for illustration i used this c...
Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    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

mwinds's avatar
mwinds
Helper I
3 years ago

Hi all,

 

I've having issues with this measure. The line chart is cumulative for some months but not for others. Here's what I've got.

 

Actual Cumulative = CALCULATE(SUM(InvoiceDetails[Total LAD Funding]),FILTER(ALL('Date'),'Date'[Date]<=MAX(InvoiceDetails[Month invoiced])))

 

 

 

Budget Cumulative = CALCULATE(SUM(Spendperlot[Total]),FILTER(ALL('Date'),'Date'[Date]<=MAX(InvoiceDetails[Month invoiced])))

 

The figures are contained in separate tables and should include all of the values in those tables.

Chart looks like this.

 

 I've set up a date table following this guide https://docs.microsoft.com/en-us/power-bi/guidance/model-date-tables and looks like this for example:

 

Data in the Month Invoiced table looks like this 

 

 

Has any one got ideas to why this isn't working properly?

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    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.