Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Forecast Table by Product, By Week

Hello, 

 

I need help setting up a table. I have a forecast by week of product variant sales, it's in Excel. I need to compare it to the actuals which I have a report set up using power query to connect to a SQL server. 

My model has week numbers, so I need to connect the week numbers in my table to the model. I also need to connect the product variant titles in my forecast table to the product variant titles in the model. I can't figure out how to structure it so that I can do both these things, and be able to write a DAX measure that subtract forecasted sales from the actuals, by model by week. Can anyone help? 

I attached a screenshot of my table right now (omitted product titles for company privacy). The product titles are on the left, and I have the week numbers at the top, but I don't think that's correct. I need to be able to create a visual like the second photo, by model by week.

Need Help

4 Replies

  • David-Ganor's avatar
    David-Ganor
    Resolver II
    4 years ago

    Anonymous ,

     

    I guess your Excel table looks like that:

    and the actuals table looks like that:

     

    If that so, you should UNPIVOT your Excel table in Power Query:

    So both tables will have: 

    Product Column, Week Column, Value (Actual or Forecart).

     

    Than - you can concatenate the Product and the Week column in both tables...that new column will serve as the Key for relationsship between them.

     

    Hope it helps  🙂

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi! 

      That helped! I got this far, but I can't combine the week number with the product in my table from the database because it will affect the rest of my report.

      Is there a different way I can establish a relationship for the product titles from my excel table to the database table? Or do I just need to create a visual for each product title.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    I could create a new column in both tables, I see now. I wish there was a way to establish 2 relationships from my excel table to the database 

    • David-Ganor's avatar
      David-Ganor
      Resolver II
      4 years ago

      Anonymous 

      The new column should serve only for the relationship purposes.

      On the visuals - you should use the original columns.

      Basically - if you'd like to do it "by the book" you should create 2 dimensions:

      dimWeek, dimProduct.

      Than - create a bridge table - contains all the options of product & week.

      Create relationship:

      dimWeek --> bridgeProductWeek.

      dimProduct -->bridgeProductWeek.

      bridgeProductWeek -->tblForcast.

      bridgeProductWeek --> tblActual.

      Than on the visuals - use dimProduct & dimWeek.

       

      Hope it clarifies. 🙂