Forum Discussion

Sperling's avatar
Sperling
Icon for Advocate II rankAdvocate II
4 years ago

Cumulative measure for tooltip

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?

Need Help

10 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      Sperling
      Icon for Advocate II rankAdvocate II
      4 years ago

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

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    4 years ago

    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's avatar
      Sperling
      Icon for Advocate II rankAdvocate II
      4 years ago

      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's avatar
        v-jingzhang
        Icon for Community Support rankCommunity Support
        4 years ago

        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

  • freginier's avatar
    freginier
    Icon for Solution Sage rankSolution Sage
    4 years ago

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

    • Sperling's avatar
      Sperling
      Icon for Advocate II rankAdvocate II
      4 years ago

      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.

  • Sperling's avatar
    Sperling
    Icon for Advocate II rankAdvocate II
    4 years ago

    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's avatar
      Sperling
      Icon for Advocate II rankAdvocate II
      4 years ago

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