locked
Projected Goal RRS feed

  • Question

  • I have the sales goals and what i catched up month to month. I want to create a column that returns me the projection for the current month. How can i calculate this? 
    • Edited by jac1994 Monday, May 29, 2017 6:59 PM
    Monday, May 29, 2017 6:58 PM

Answers

  • Hello,

    So, one way to look at this is to build the model that would consist of 3 tables:

    • Transactions - fact table that captures sales of our products throughout the year
    • Sales Goal Table - separate table with annual sales goals for our products
    • Date Dimension - standard dimdate we use in dimensional modeling.

    Here is the source sample



    The logic would be to create a year to date sales measure from ongoing product transactions and divide it by annual sales goals in order to get that ratio of sales goals met.

    % Sales Goal :=
    IF (
        [Quantity Sold] > 0,
        DIVIDE (
            CALCULATE (
                [Quantity Sold],
                FILTER (
                    ALL ( DimDate ),
                    DATE ( YEAR ( DimDate[Date] ), 1, 1 )
                        = DATE ( YEAR ( MAX ( DimDate[Date] ) ), 1, 1 )
                        && DimDate[Date] <= MAX ( DimDate[Date] )
                )
            ),
            SUM ( SalesGoal[Annual Sales Goal] ),
            0
        ),
        0
    )

    Here is our result



    *** zero shows months without sales



    Tuesday, May 30, 2017 8:37 PM

All replies

  • Hi Jac1994,

    Thanks for your question.

    To solve your issue more efficiently, would you please share sample data and the expected results ?

    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, May 30, 2017 3:17 AM
  • Hello,

    So, one way to look at this is to build the model that would consist of 3 tables:

    • Transactions - fact table that captures sales of our products throughout the year
    • Sales Goal Table - separate table with annual sales goals for our products
    • Date Dimension - standard dimdate we use in dimensional modeling.

    Here is the source sample



    The logic would be to create a year to date sales measure from ongoing product transactions and divide it by annual sales goals in order to get that ratio of sales goals met.

    % Sales Goal :=
    IF (
        [Quantity Sold] > 0,
        DIVIDE (
            CALCULATE (
                [Quantity Sold],
                FILTER (
                    ALL ( DimDate ),
                    DATE ( YEAR ( DimDate[Date] ), 1, 1 )
                        = DATE ( YEAR ( MAX ( DimDate[Date] ) ), 1, 1 )
                        && DimDate[Date] <= MAX ( DimDate[Date] )
                )
            ),
            SUM ( SalesGoal[Annual Sales Goal] ),
            0
        ),
        0
    )

    Here is our result



    *** zero shows months without sales



    Tuesday, May 30, 2017 8:37 PM