Forum Discussion

hidenseek9's avatar
hidenseek9
Icon for Post Patron rankPost Patron
7 years ago
Solved

Help needed with calculating PL forecast. DAX help needed

Hello Power BI community,

 

Am struggling with setting up a DAX to calculate net sales automatically.

Please find below as a sample data.

Sample Data

 

I have 3 tabs (Price Data, Actual and Demand) and I want Power BI outcome to look exactly like "Final Outcome" tab.

Below is an explanation on the dataset.

  • Actual tab: Actual performance of the products YTD (Year to Date)
  • Demand tab: Volume forecast on products YTG (Year to Go)
  • Price Data: standard price on each product (from selling price to material cost to all the way down to logistics cost)

Objectives:

  • I would like to merge Actual tab and Forecast tab, then
    1. For products already in the market (Air Jordan 1 to 3), based on historical selling price, material cost, industrial cost, logistics cost, I would like to calculate cost based on Demand volume forecast.
    2. For products already in the market (Air Jordan 1 to 3), based on "Price Data" tab, would like to apply rebate % that matches with date and product name. (2% for Air Jordan 1 to 3 for rest of the year, but the assumption could change month to month)
    3. For products that are not released yet, would like to calculate net sales, rebate rate, material cost, industrial cost, and logistics cost, based on "Price Data" tab.

If you could help me with this, this will be a huge help.

 

Much thanks in advance.

 

H

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    Hi hidenseek9,

     

    I created a sample DAX formula for your reference, please create a calculated table with it:

    Table =
UNION (
    SELECTCOLUMNS (
        Actual,
        "Date", Actual[Date],
        "Product", Actual[Product],
        "volume", Actual[Volume],
        "Price", Actual[Price],
        "Rebate %", Actual[Rebate %]
    ),
    SELECTCOLUMNS (
        FILTER ( Demand, Demand[Date] > MAXX ( Actual, Actual[Date] ) ),
        "Date", Demand[Date],
        "Product", Demand[Product],
        "volume", Demand[Volume],
        "Price", LOOKUPVALUE (
            'Price Data'[Price],
            'Price Data'[Date], Demand[Date],
            'Price Data'[Product], Demand[Product]
        ),
        "Rebate %", LOOKUPVALUE (
            'Price Data'[Rebate %],
            'Price Data'[Date], Demand[Date],
            'Price Data'[Product], Demand[Product]
        )
    )
)

     

    Best regards,

    Yuliana Gu

2 Replies

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

    Hi hidenseek9,

     

    I created a sample DAX formula for your reference, please create a calculated table with it:

    Table =
UNION (
    SELECTCOLUMNS (
        Actual,
        "Date", Actual[Date],
        "Product", Actual[Product],
        "volume", Actual[Volume],
        "Price", Actual[Price],
        "Rebate %", Actual[Rebate %]
    ),
    SELECTCOLUMNS (
        FILTER ( Demand, Demand[Date] > MAXX ( Actual, Actual[Date] ) ),
        "Date", Demand[Date],
        "Product", Demand[Product],
        "volume", Demand[Volume],
        "Price", LOOKUPVALUE (
            'Price Data'[Price],
            'Price Data'[Date], Demand[Date],
            'Price Data'[Product], Demand[Product]
        ),
        "Rebate %", LOOKUPVALUE (
            'Price Data'[Rebate %],
            'Price Data'[Date], Demand[Date],
            'Price Data'[Product], Demand[Product]
        )
    )
)

     

    Best regards,

    Yuliana Gu