topic Cumulative values on graph where date slicer doesn't recalculate cumulative value in DAX Commands and Tips

Cumulative values on graph where date slicer doesn't recalculate cumulative value

james44452 — Tue, 27 Feb 2024 11:59:16 GMT

Hi,

I have a line graph with cumulative revenue over time using the following measures:

Cumulative revenue =

CALCULATE(

[Revenue by date],

FILTER(

ALLSELECTED('calendar'[Date]),

ISONORAFTER('calendar'[Date], MAX('calendar'[Date]), DESC)

)

This measure uses the measures below:

Revenue = SUM('Project financials'[Value])

Revenue by date =

CALCULATE([Revenue],

USERELATIONSHIP('calendar'[Date], 'Project financials'[Date])

)

Some dummy data in my Project Financials table looks like this:

Project	Date	Value
Project x	1 Jan 22	£10k
Project x	1 Feb 22	£50k
Project x	1 Mar 22	£100k
Project y	1 Jan 22	£80k
Project y	1 Feb 22	£150k
Project y	1 Mar 22	£160k

When I plot this in a line graph the cumulative value recalculates based on the start date chosen in the date slicer. However, I need it to always calculate cumulative revenue from the start of the project and the date slicer just changes which period we're looking at. So, let's say a project began in January 2022, the cumulative revenue by January 2023 was £1m and the cumulative revenue at January 2024 was £2m. If the date slicer was set with a start date of January 2023 this should still lead to my cumulative measure giving the cumulative value at January 2023 as £1m.

Re: Cumulative values on graph where date slicer doesn't recalculate cumulative value

lbendlin — Tue, 27 Feb 2024 21:14:32 GMT

Your explanation is not clear to me.

Please provide sample data that covers your issue or question completely.

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Cumulative values on graph where date slicer doesn't recalculate cumulative value

james44452 — Thu, 29 Feb 2024 11:48:46 GMT

Apologies for note being clearer. The link below to a simple pbi file with fake data shows what I have currently. The issue I have is that if I move the date slicer to 01/03/2023 the cumulative value for March resets to March's value and ignores the values in January and February. The png file (link below) has a black line drawn on it which shows how it should look when the slicer is moved to 01/03/2023.

Example pbix

Mark up of graph

I can't just create a cumulative column in any of the tables either as the real dashboard uses some measures that draw from various tables to create a total.

I hope that makes things clearer.

Many thanks,

James

Re: Cumulative values on graph where date slicer doesn't recalculate cumulative value

lbendlin — Sat, 02 Mar 2024 02:53:29 GMT

The link asks for access. Please check.

Re: Cumulative values on graph where date slicer doesn't recalculate cumulative value

james44452 — Tue, 05 Mar 2024 13:29:23 GMT

Sorry Ibendlin. I have changed the settings of the files and the links should work for you now.

Many thanks

James

Re: Cumulative values on graph where date slicer doesn't recalculate cumulative value

lbendlin — Wed, 06 Mar 2024 01:47:49 GMT

You were looking for the zoom slider.

Re: Cumulative values on graph where date slicer doesn't recalculate cumulative value

james44452 — Wed, 06 Mar 2024 14:55:14 GMT

Thank you and I didn't realise that it would be so simple!