Get actuals up to a certain date and show forecast values

[kwats]

Hi. I have an actuals vs forecast report where I need to show actuals and forecast on the same bar chart, shown below. My challenge is to show actuals that have dates before the selected book name effective date and show forecast dates greater or equal to the selected book name. 

The data is tricky because I need to filter the chart based off the selection in a slicer. The slicer values are text names of account books, so they aren't dates but they are mapped to an effective date in the data table. The book names in the slicer are also considered forecast values in addition to other the 'Forecast' values. It's a weird way our department has these things named.

So if you choose 22Mar in the slicer, the table would show the following:

• Actuals from the earliest point in time up to 2/1/2022
• All rows corresponding to 22Mar in the Book Name column
• All rows corresponding to Forecast in the Book Name column

If you choose a book name in the slicer, it only shows dates corresponding to those rows, so it will not show earlier dates going back many years. I saw something similar to this in the forum but their table grows wide as new books are created for the corresponding month. I hope this makes sense. Also, I'm wondering if it's possible to show the bar chart values as year-to-date values if no book name sare selected. So as of today, there would be a 22Jun book in the table that the actuals would go up to.

Here's a link to a sample Power BI file to work with.

https://1drv.ms/u/s!Angb1ZSXK0lmg_ZkW35rsTFrRWL-WA?e=mk2enA

Thanks

[ALLUREAN · Accepted solution]

Hi, @kwats 

Check my blog it might help in solving your case.

https://allure-analytics.com/index.php/2022/06/11/combine-actuals-forecast-in-power-bi/

How to create calendar table: 

https://allure-analytics.com/index.php/2022/05/14/standard-calendar-date-table-in-power-bi/

[kwats]

Hi @ALLUREAN , thanks for the link to your blog. It gave me some ideas as to what I needed to do. I bookmarked your blog since I know I'll run into this into the future haha. For my problem, I needed to unlink the relationship I had from the date dimension table to the fact table and do it within dax. I was able to keep everything in one table but I may break it up on another version and utilize the dax you provided.

[Jbeaman85]

This is something I need to do but have no idea where to start.

I will be linking this report via SQL to our Sage 200 system. 

I need to create:

- our financial year dates (no idea how)

- nominal transactions for actual

- forecast output based on order book details 

- historical actual

i know which tables tk get the data from but need some ideas and guidance in building the report 

[speedramps]

@JJbeaman85

If you  "have no idea where to start" then it might be best you start with this learning plan before commencing with the company finacial reports. 😀😀

Start with this calendar video and do all the courses a few times 

Then progress to this more advanced video. 

Thanks for reaching out for help.

I have helped you, now please help me by giving kudos.

Remeber we are unpaid volunteers.

Click the thumbs up and accept as solution button. 

One question per ticket please. If you need to extend your request then please raise a new ticket.

You will get a quicker response and each volnteer solver will get the kudos they deserve. Thank you !

[speedramps]

I am not sure I underatand you but I think you need 3 tables

A Calendar table with contiguous dates

A forcast table with a date column using your busienss date logic

A actual table with a date column using your busienss date logic

Create relationship  calendar 1:m forecast date

Create relationshio  calendar 1:m actual date

Remove your fact table date from visuals and use the calander date instead.

Voila! You can now compare fgures based on the same date.

If you need more help then please provide examples of the input data (as tables not screen prints to we can import them and build solution)

and the desired output with clear descriptions.

Remember not to share private data.

[Jbeaman85]

Thanks for the reply. 
I will certainly do this. As I say I know what SQL tables I need and can link that data, but it is this part I don't know how to do.

any other tips I should be considering? I'm fairly new to Power BI

[speedramps]

Hi again Jbeaman85

These videos are a good free way to learn power BI. Thet are how I am many other super user learnt. 

Click here to start learning 

Remeber we are unpaid volunteers.

Click the thumbs up and accept as solution button now. Thank you !! 😎