Forum Discussion

mwinds's avatar
mwinds
Helper I
3 years ago

Cumulative line chart

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 

There is a relationship with "month invoiced" and "Date" so it works perfectly if i wasn't trying to make a cumulative line chart.

 

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

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    mwinds , You should date/period from date table on axis and measure should be like

     

    Budget Cumulative =
    var _max = maxx(allselected(InvoiceDetails) , InvoiceDetails[Month invoiced])
    return
    CALCULATE(SUM(Spendperlot[Total]),FILTER(ALL('Date'),'Date'[Date]<=MAX('Date'[Date]) && 'Date'[Date]<= _max ))

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    mwinds , You should date/period from date table on axis and measure should be like

     

    Budget Cumulative =
    var _max = maxx(allselected(InvoiceDetails) , InvoiceDetails[Month invoiced])
    return
    CALCULATE(SUM(Spendperlot[Total]),FILTER(ALL('Date'),'Date'[Date]<=MAX('Date'[Date]) && 'Date'[Date]<= _max ))

    • mwinds's avatar
      mwinds
      Helper I
      3 years ago

      Hey, 

      Thanks for the reply. 

      The chart looks like this now if i chose catergorical in the x-axis type 

      or like this if i choose continuous in the x-axis type.

       

      Any way i can get it to show every month and for the line to go up gradually rather than in chunks.

      If i choose to add Month name and year in the x-axis, sort by month and drill down, i get this. Not too sure what's going on here.

       

      File is here https://drive.google.com/file/d/1yXye3LVBp-ZA3Bz31-9_notHQXTaPTQh/view?usp=sharing