Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
mwinds
Helper I
Helper I

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.

mwinds_0-1662734403257.png

 

 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:

mwinds_1-1662734504382.png

 

Data in the Month Invoiced table looks like this 

mwinds_0-1662735615461.png

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?

3 REPLIES 3
amitchandak
Super User
Super User

@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 ))

Hey, 

Thanks for the reply. 

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

mwinds_0-1662993112418.png

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

mwinds_1-1662993214491.png

 

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.

mwinds_2-1662993390339.png

 

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

 

amitchandak
Super User
Super User

@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 ))

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.