Replicating Excel process in Power Bi

[Anonymous]

Hello Community, 

My boss has asked me to replicate a report that she currently does in Excel.    The process involves downloading (at start of each month) the forecast data from our CRM.    Our CRM does not capture "snapshots", so these downloaded files act as those.    Which means, there is a file for Jan....for Feb....for March...etc  

She compares what was forecasted at the start of each month, to what was actually ordered.   The "ordered" data comes from our ERP and it is a typical transactional Fact table....with values by row for each day's orders.  

Here is the tricky part:    to calculate January's accuracy, she grabs the forecasted value from a tab (sheet) in her workbook.   Example below.  So the $200,000 value is coming from the 'Jan 19 Detail'  sheet in her workbook.     The "Actual" value is cut and pasted from a different workbook.     For February, she would grab the CRM forecast data from the 'Feb Detail' sheet.    She puts all the data in a table, and creates a chart visual  (with months on the  X  axis).    So imagine the information below for each month of the year laid out in table visual.   She references these values for her chart.   

Because each month's calculation is unique to that month's forecast, and she references "sheets" in her workbook to grab those values.....how to emulate this in Power Bi?    I have imported each of her monthly files into Power Bi, but am perplexed as to how to go about emulating her "reference the sheet for each month" process.    And in particular, how to use each month's specific values to do the calculations.   In other words, I have to compare the forecast value in Jan's file to our ERP actuals.....then compare the forecast value in Feb's file to our ERP actuals....etc etc.    But the, for example, how to display those totals in a chart visual.   

Any advice is much appreciated!

='Jan Detail'!F14

Jan                Jan            Jan                               

FCST             Actual       Accuracy

$200,000      190,000        95%

[amitchandak]

You need to bring data together. Create common  tables/dimension table. date or month table and analyze together.

Can you share sample data and sample output.

[Anonymous]

Additional information: 

This is how the table in Excel would look  (but there would all 12 months across the top).    Each month's FCST total would come from a specific "sheet" in the Excel file.   (the values from those sheets actually come from a workbork per each month).    I have imported each of those month's files.    You can see this below.   

I need to compare the Jan FCST sum  (Potential) to the actual order value for that month.    sum of potential / sum of orders

Same for Februrary...but the FCST information comes from a different source file    sum of potential / sum of orders 

etc.....

My expected output would be to create a table of Jan's FCST, Feb's FCST.     

Forecasted VS Actual Revenue	Feb FCST	Feb ACTUAL	%
ERP Orders	$           3,151,542	$           2,191,630	70%
80% FCST	$           1,171,291	$               798,109	68%