Hi I have been trying to establish the right model that will hopefully give me the following output:
Establish the correct relationship between actuals and the forecast tables in order to run some standard comparison metrics .
Be able to filter by person, project
Planned Headcount as a percentage and being able to compare this against the actual hours booked by an engineer to a project.
Once I guess the foundation / model is structured my thoughts are to add additional tables such as project status, risks and defects into the mix.
The cost analysis I’m fairly content can be done in DAX from some of the great instructional videos that I’ve seen over the last month on here.
Results: so far I have constructed and deconstructed and built again but only seem to get one side of data, either actual or the forecast. I have tried unique IDs in each fact table and then create a bridge via a new query and creating a unique column, which I did manage to create a one to many between the two but was unsuccessful once I tried it in a table in visualisation.
Even asking and posting this doesn’t sit well as I do love a challenge. I can only assure you that I have placed considerable time and effort (weeks lol) to get past this issue but its time to get some help ’ please’.
Due to data constraints I am unable to submit the actual data, however I have constructed some tables representing some columns that appear in the originals that I hoping along with the above text to help people understand what I need.
Thank you and pleas be kind, you can probably tell I very new to this but loving the journey
Hi @Pete, 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 </>.
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.
Thank you, hoping for a response on this one as soon as possible I guess. I have been stuck on this for a good few weeks. I was advised the that upstream is a better way which is why I’m keen to have an understanding on the model as a first call rather than DAX solutions. Actuals vs forecasted would be a massive boost for me as a programme manager. Cannot wait to see any solutions on this, fingers crossed
I just downloaded the files and am not quite sure how to collate all that easily. I have worked in finance world with actual, budget, forecast and find the easiest way to set the model up is to bring data into a single fact file with a column marked act, bud etc to denote the scenario. If the fact file is large create a dimension table with act bud etc.
The advantages here is that DAX becomes easier with single fact file. Write a base measure then wrap a CALCULATE around it to filter act, fcst etc. Model will also need fewer relationships.
Your data looks like you have materials and labour. So I would create two tables upstream for each of actual and forecast with materials and labour all worked out. I would work on monthly granularity to start with and set dates as last day of month for both forecast and actual.
Once you’re comfortable with that work on the dates piece. Actuals will have daily amounts, forecast is likely to be monthly in raw data. So expand the forecast across dates in month either at source or with PQ.
You’ll then be in a position to write some pretty easy DAX for mats and labour cost and with measure branching total the pair and filter your scenario.
Hi @Pete, did the response provided by @BINavPete 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!
Thank you kindly for taking the time to response I really do appreciated it. I have been away so apologies for the lack in response this week. For your awareness an you’ll have probably guessed I am a complete novice in this area and after many hours visual study with all the lessons that Sam and others have here I have only just started the doing phase and now trying to relate a very raw knowledge of everything to my working environment (Programme Manager in Defence).
If at all possible could I spend a few days looking at your suggestions to see if I can piece this together and then after come back crying for help lol (hopefully not). Conversion of the granularity seems to be key and your right we only brief monthly and sum in quarters.
Hope that works
Once again many thanks, although some of this is initially frustrating, i can see a massive benefit and efficiency in this which is why its definitely worth taking the journey
I think due to my lack of experience there are probably many easier ways to do this , merging or appending the forecasts I can see for one. One of my biggest problems was trying to join Actuals and Forecasted (merging with different types and granularity). I have realised I need a lot more understanding of many to many and joining them so I chose to keep them separate for now but will endeavour to have a look at this area.
I also tried to append the forecasts, however, I encountered Forecasted Hours, FTE and Cost fell into the same column on an append causing confusion due to the values in the column being different types such as FTE = % Hrs = Decimal and Cost was cost, problem being column could have only on data type.
However I guess for a first ever attempt at creating a model I really pleased with my progress so far, so be nice lol.
That said, I’ve attached the PBIX for anyone to comment on and give me ideas on how to improve this (prepared for many ) Areas as to whether this works well enough as a model before I add measures and Dax. How to merge Forecast and Actuals or whether they should be separate.
As a Programme Manager this model design (project) will be my most used so any response I can get from you lovely people would be fantastic.
Hi @Pete - You should not Append your Forecast table rather use Merge to get all Forecast data in single Fact table. Further you can append with your actuals table by adding/removing columns to make Columns same in both Actuals and ForeCast table. Before Append add KeyColumns to differentiate b/w Actuals and Forecast Data.
Refer to attached PBIX file where I have inserted “Forecast_Budget” and “Forecast_Hours” into Excel files and use Merged to create a single ForeCast table. In new ForeCast table added KeyType to display it is ForeCast Data.
Can try creating a Model like this and once your model is complete, can use DAX to Filter the required columns and keyType in Calculations.
Hi @Pete, did the response provided by @ankit 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!
Apologies for the slow response, thank you kindly for taking time to look at this I will take everything ounce onboard here and further develop this using your key guidance.
It’s very early days for me in BI but loving the journey and being guided and having this forum is so helpful for development
