Cumulative measure for tooltip

[Sperling]

I'm interested in a cumulative measure that is based on a dynamic period for a tooltip.

I have a table with the last 31 days showing a daily index, and when I plug a cumulative measure into it, it works fine. However when I instead apply it as a tooltip it only shows the daily values.

I could solve it using the TOTALMTD measure, however that measure is not useful for periods that go across two different months.

This is what works as cumulative total in table, but not it tooltip:

Result =

VAR Filtered =

FILTER(

ALLSELECTED('Date'[Date]),

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

)

RETURN

CALCULATE(

'Sales'[Revenue],

Filtered

)

Any suggestions?

[v-jingzhang]

Hi @Sperling 

Use ALL instead of ALLSELECTED.

Cumulative = CALCULATE(SUM(Sales[Revenue]),FILTER(ALL('Date'[Date]),ISONORAFTER('Date'[Date], MAX('Date'[Date]), DESC)))

And you can also use below measure for cumulative total. 

Result = 
var _maxDate = MAX('Date'[Date])
return
CALCULATE(SUM(Sales[Revenue]),ALL('Date'[Date]),'Date'[Date]<=_maxDate)

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Sperling]

Thanks for the reply!

I don't know if I'm missing something, but when I use my dataset with older sales values as well, I get a cumulative measure up to the current max date.

I'm interested in a cumulative measure for the days in my date filter for the page.

[v-jingzhang]

Hi @Sperling 

Do you put Date table's Date column on X axis of the column chart and into your date filter? How do you set your date filter? Is it possible to provide a sample pbix here (after removing sensitive data)? 

Regards,
Jing

[Sperling]

@v-jingzhang 
Hi again,

I've identified the issue in your pbix file too. It happens when I add a slicer based on date and adjust that.

When I add a slicer to your file and move the first date a bit forward, the cumulative tooltip still shows the cumulative value as if the slicer date filter was ignored.

[parry2k]

@Sperling I did a video on this, check it out here https://youtu.be/THY8y8UZuww

✨ Follow us on LinkedIn and  to our YouTube channel

Check my latest video on Filters and Sparklines https://youtu.be/wmwcX8HvNxc

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[Sperling]

Replying here as well.
Thanks for the answer, but unfortunately this just gives me a cumulative value for the whole period before "max date", whereas I'm interested in specific period ranges based on my date filter on the page (e.g. last 31 days).

[Sperling]

Any ideas as to why it's not working?

Since the only reply said it works I tried to make a simple test by making my own excel sheet to import into Power BI with 2 columns.

One for every date in a month and one with a "sales value" of 1 for every date entry.

This is the result:

As it can be seen the cumulative measure works fine in the graph, but not in the tooltip.

[Sperling]

I've added the TOTALMTD measure to show how that works, but not my cumulative one.

[freginier]

why do you say it doesn't work? I use your measure as tooltip in bar chart and it works.

[Sperling]

I've tried recreating the steps and it doesnt work for me.

Even when I make a line/bar chart with dates on the axis and the cumulative measure as the value, I can see the cumulative measure working fine. However when I add the same cumulative measure as tooltip to the graph it shows the daily value instead of the cumulative one.