Forum Discussion

Kieran_choudary's avatar
Kieran_choudary
Icon for Helper I rankHelper I
9 years ago

Month on month in tooltip, help please

Hi guys,

 

Im trying to include the monthly growth in the tool tips of my monthly reveue table, i want it so that when you hover over a month the tool tip will show the growth or negative growth as a percentage on the month previous to the one you have selected.

 

Does anyone know if this is possible and if so how to do it?

 

I have a date table and revenue data

 

so in this example it would show a negative to december growth

 

Thank you in adavnce!

 

 

Need Help

4 Replies

  • Datatouille's avatar
    Datatouille
    Icon for Solution Sage rankSolution Sage
    9 years ago

    Hi Kieran_choudary

     

    Yes it is possible.

     

    1) you need a DAX measure which computes the Previous Month Sales value.

    You need to have a proper CalendarTable (dedicated to Time Intelligence) linked to your Revenue Table in your model - which seems to be DimDate in your case.

     

    [Total Sales] = Sum(Revenue[SalesAmount])

    [Sales Last Month] = Calculate ( [Total Sales] ; PreviousMonth(DimDate[Date]) )

    2) You can now compute the sales % evolution with the measure below:

    [SalesPctChange] = Divide ( [Total Sales] ; [Sales Last Month] ) - 1   which you can format as a percentage in the modeling tab.

     

    3) Use the measure [SalesPctChange] in the tooltip section of your revenue monthly chart !

     

     

    • bella's avatar
      bella
      New Member
      9 years ago

      Thank you, Datatouille!

      Could you show me how to make the proper calendar table, which linked to the revenue column?

      Because the revenue rows are counted base on more times on one day, so deal with the revenue per month, we have many rows of revenue. 

    • v-sihou-msft's avatar
      v-sihou-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      9 years ago

      Kieran_choudary

       

      In this scenario, do you have a full calendar date table with relationship built to the fact table?

       

      If not, it will always show blank when you use PREVIOUSMONTH() in your calculation. You can use a table visual to test if your measure can be sliced by measure. When your measure can be sliced by YearMonth, it should be displayed in Tooltip.

       

      If you only have Year and Month column in your table (no full calendar table), you can use a calculated column to achieve your goal. You need to have a Year column and Month Number column in your table. Then you can create a calculated column like below:

       

      Previous Month Total Column = CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Year]=EARLIER('Table'[Year]) && 'Table'[Month Number]=EARLIER('Table'[Month Number])-1))

       

      Then you just need to drag this column into Tooltip to have it displayed in your chart.

       

      Regards,