cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Robapottamus Frequent Visitor
Frequent Visitor

Cumulative Line Chart to compare performance of multiple products over time

Hi - I am trying to compare the performance of multiple products over time (years), using a line chart.
At the moment I am using a Measure called Cumulative Value to sum the cost and revenue for each year individually, so getting each product each year is fine.
What I want to do now is extend this to accumulate year on year, over time. In a line-chart format. But I can't seem to do this in a line-chart format. This is pretty easy to do in Excel, see photo below (couldn't see how to upload the full file).
Any suggestions?
FYI here is my current formula, Cumulative Value = CALCULATE(SUM('Data Table'[Value] ),FILTER(ALL('Data Table'[Date]),('Data Table'[Date]<=MAX('Data Table'[Date]))))

Sample.gifSample chart (easy in Excel)

 

1 ACCEPTED SOLUTION

Accepted Solutions
KamK Frequent Visitor
Frequent Visitor

Re: Cumulative Line Chart to compare performance of multiple products over time

Hi @Robapottamus

 

For time intelligence functions Ive fount it best to have a separate date table (with unique dates) and create a relationship with your exsisting data table.

 

Assuming your new date table is called  DateKey and the column called Date , try the following

 

Cumulative Value = CALCULATE(SUM('Data Table'[Value] ),FILTER(ALL(DateKey),DateKey[Date]<=MAX('Data table'[Date])))

 

Hope this helps

 

 

 

 

2 REPLIES 2
KamK Frequent Visitor
Frequent Visitor

Re: Cumulative Line Chart to compare performance of multiple products over time

Hi @Robapottamus

 

For time intelligence functions Ive fount it best to have a separate date table (with unique dates) and create a relationship with your exsisting data table.

 

Assuming your new date table is called  DateKey and the column called Date , try the following

 

Cumulative Value = CALCULATE(SUM('Data Table'[Value] ),FILTER(ALL(DateKey),DateKey[Date]<=MAX('Data table'[Date])))

 

Hope this helps

 

 

 

 

Robapottamus Frequent Visitor
Frequent Visitor

Re: Cumulative Line Chart to compare performance of multiple products over time

Thanks KamK, that seems to do the trick. I haven't considered the idea of using a separate Dates table previously so will need to get my head around how I can use these going forward. Cheers!

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 111 members 1,696 guests
Please welcome our newest community members: