Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

KPI Visualization - Using LY's Monthly Sales as Goal for TY's Monthly Sales

Hi All!

 

I am relatively new to Power Bi and am creating a dashboard for our Inside Sales Team. I am trying to create a KPI of Monthly Sales Totals and the goal being last year's monthly sales +15% (for example, January 2019's sales goal would be January 2018's total sales + 15%).

 

Any suggestions would be much appreciated, thanks!!

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi Anonymous

     

    • Create a Slicer on Sales Months
    • Place the Sales Measures  Sales = SUM(Sales_Fact[Sales_Amt]) in the "Indicator" field in the Field tab
    • Place the Months in "Trend Axis" field in the field tab. Please make sure the Month name is sorted using the Month number.
    • Create Target Sales Measure  LYSales Target = CALCULATE(SUM(Sales_Fact.Sales_Amt),DATEADD(Dates[Date],-1,YEAR)) * 1.15 and place this measure in the "Target goals" field in the field tab

    Your KPI visual will be ready to use now. Hope this helps.

     

    Thanks
    Raj

     

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Anonymous

     

    • Create a Slicer on Sales Months
    • Place the Sales Measures  Sales = SUM(Sales_Fact[Sales_Amt]) in the "Indicator" field in the Field tab
    • Place the Months in "Trend Axis" field in the field tab. Please make sure the Month name is sorted using the Month number.
    • Create Target Sales Measure  LYSales Target = CALCULATE(SUM(Sales_Fact.Sales_Amt),DATEADD(Dates[Date],-1,YEAR)) * 1.15 and place this measure in the "Target goals" field in the field tab

    Your KPI visual will be ready to use now. Hope this helps.

     

    Thanks
    Raj

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Rajendran,

       

      Thanks for the help! Everything is set up as indicated, however, this is the response that I receive after inputting the formula: "A function 'DATEADD' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

       

      This is the formula I put in: LYSales Target = Calculate(sum(Sales[XTNDPRCE]),DATEADD('Calendar'[Date],-1,year)*1.15) 
       

       

       

  • v-danhe-msft's avatar
    v-danhe-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi Anonymous,

    Could you please offer some sample data and post your desired result if possile?

     

    Regards,

    Daniel He

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hello!

      Attached is my sales table, calendar table, and "desired result". Pertaining to the KPI visual, the $482,537 is the current sales amount for the present month, and the goal will be the total sales amount of the same month from LY with 15% added. For example, my data range is 01/01/2018-Present, so the sales department's January 2019 sales goal is January 2018's total sales +15%.