Creating a new table by pulling data from different tables based on conditions

Power Query
1

Hello,

I’m trying to create a new sales table that shows full year sales figures by combining “Actual Sales” table and “Forecasting” table. So the new table will have mix of year-to-date actual sales and year-to-go forecast. This table will update automatically every week as actual sales gets updated. Both “Actual Sales” table and “Forecasting” table have numbers at weekly level. Moreover, what makes it even more difficult is that our company uses custom (fiscal) calendar and not normal calendar dates, which I learned how to work it by watching one of Sam’s videos on time series analysis.

To give an example of what I’m trying to do…

  1. “Actual Sales” table:
    This table has columns as below. In this example, YTD actual sales happened from Jan WK1 to Feb WK3. In other words, I’m currently sitting on the middle of Feb WK4.
    This table gets updated automatically every week with new weekly sales results as year goes by.

Year/Moth/Week of month; Customer; Product; Actual Sales Volume
2021/Jan/WK1; FL; a; 20;
2021/Jan/WK2; MN; b; 40;
2021/Jan/WK3; GA; c; 10;
2021/Jan/WK4; PA; a; 50;
2021/Feb/WK1; FL; f; 80;
2021/Feb/WK2; GA; b; 30;
2021/Feb/WK3; AZ; a; 10;

  1. “Forecasting” table: this table has weekly forecast from 1st week of Jan to the 5th week of Dec. It has the same fields as the “Actual Sales” table.

  2. New table that I’m trying to create: Combining the two tables with a condition/formula.
    In this new table, I’m trying to grab actual sales from the “Actual Sales” table for the weeks that sales already happened. And then, for the remaining weeks for the year, it grabs forecast from the “Forecasting” table. The purpose for this table is to have a view that provides the full year estimated sales at weekly level by combining ‘Year To Date actual sales’ and ‘Year To Go forecast’.
    As week passes, this table will automatically replace the forecast number of previous week with actual sales. So the new table should look like below…

Year/Moth/Week of month; Customer; Product; Volume; Actuals/Forecast
2021/Jan/WK1; FL; a; 20; Actuals
2021/Jan/WK2; MN; b; 40; Actuals
2021/Jan/WK3; GA; c; 10; Actuals
2021/Jan/WK4; PA; a; 50; Actuals
2021/Feb/WK1; FL; f; 80; Actuals
2021/Feb/WK2; GA; b; 30; Actuals
2021/Feb/WK3; AZ; a; 10; Actuals
2021/Feb/WK4; FL; a; 100; Forecast
2021/Mar/WK1; AZ; c; 200; Forecast
2021/Mar/WK2; AZ; f; 300; Forecast
.
.
.
2021/Dec/WK5; FL; a; 150; Forecast

Sorry for the long question but hope that you understand what I’m trying to do.
It would be great if someone could guide me on which formulas to use in query editor or DAX to accomplish this. I haven’t watched all Sam’s videos so if there is a video that talks about this type of situation, I’d appreciate it if someone could let me know.

Thank you for your help in advance.

2

Hi @ScottRoh. To help the forum members more easily visualize your issue and desired outcome, could you perhaps create your 2 sample datasets in an Excel file, then load them into a PBIX? (Given your custom calendar notes, it sounds like an even better idea.) The dataset and work-in-progress PBIX, along with an Excel mock-up of exactly the outcome you’re trying to achieve with your sample data, would help the forum members get a running start at your issue.
Greg

3

Hi @ScottRoh, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.
  • When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.

image

  • Use the proper category that best describes your topic
  • Provide as much context to a question as possible.
  • Include the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Please also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file.

4

=> Just append them: Table.Combine({Actuals, Forecast})
Then filter out all lines that are forecast and not older than the last Actuals. You can make your live (filtering) easier if you have a date column in both tables.

5

Combining YTD Sales and Forecast Tables.pbix (38.9 KB) Desired Output Table.xlsx (24.5 KB)

Thank you for the suggestion. I’ve attached sample pbix file with the sample tables, and desired outcome of the report.

  1. Do you still think appending would work since the two files have the same column structure?
    If you see the “Desired Output Table”, there needs to be an extra column that tells whether the volume is ‘Sales’ or ‘Forecast’. Are there formulas in Query Editor that will automatically populate ‘Sales’ for the data that come from “Actual Sales” table and populate ‘Forecast’ for the data that come from “Forecast” table? Or would I need to insert these columns before I append the two tables?

  2. Also, unfortunately there is no date field in the actual tables that I work with. There are only weekly indicators. When a new week of sales gets updated in the “Actual Sales” table, would there be a way to automatically update the append table to replace forecast with the actual sales? As an example, in the sample tables that I uploaded, the final week that actual sales has updated is 2021/02/Wk3 which is the 7th week of 2021. When 2021/02/Wk4 sales updates, can I replace the 2021/02/Wk4 forecast numbers with the actual sales by using DAX or some formula in Query Editor when designing the appended table?

6
  1. You had described the additional status column in your first post. I did not think that it would cause you any issue to create it. You can add in each source table a custom column called Status or Actuals/Forecast and simply write “Actuals” respecitively “Forecast”.
  2. Dates are easy to compare. 2021/02/Wk4 you can not compare with 2021/02/Wk2.
    => But you can easily change 2021/02/Wk4 to 2021024. That is a numerical value which you can compare with 2021023.
    And you can also easily get the MAX of these values of the Actuals table.
    => Keep every Forecast line wich is > MAX Actuals. That would automatically adapt when MAX Actuals change.
7

Thank you for your help, Matthias. I think I’m getting there but have a couple of follow up questions.

  1. From your answer for No. 2, can you provide a sample function that I can use to keep Forecast lines > Max Actuals?
    I don’t think you meant me to go to the appended table each week and manually filter to keep Forecast lines >Max Actuals. So, is there a way to accomplish this after I append the two tables? or do I need to do something when I’m preparing the two source tables before appending them?

  2. Please see if I understood the detailed steps to get to the desire outcome. Below are the steps that I think I should take…
    Step 1) In the Query Editor, add a ‘Status’ column in both “Actual Sales” and “Forecast” tables and write “Actuals” and “Forecast” respectively.
    Step 2) Then, for the “Forecast” table, use some function to keep Forecast lines that are >Max Actuals and remove Forecast lines that are <Max Actuals.
    Step 3) Then, append the “Forecast” table to “Actual Sales” table to get the final outcome table.
    Does this sound right?

Again, thank you so much for walking me through this case… :smile:

8

Scott, you can first filter manually and then replace in the Function bar your dummy filter with
List.Max(Actuals[YourDateSurrogateColumn]). YourDateSurrogateColumn I described above.

  1. Your order makes the filtering less complex, then in my original suggestion. So it is good. You just have to append query as new Table.Combine({Actuals, Forecast})
9

Hi @ScottRoh , did the response provided by @Matthias help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

2 Likes
10

Hi @ScottRoh, we’ve noticed that no response has been received from you since the 3rd of March. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

11

Hi @ScottRoh, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!