How to compare 2022 actuals with 2021 also with 2022 forecast in the same table? Many thanks!

[00claire]

Hi guys,

I'm new to PBI and much appreciated if you can help me with the below question (Probably a quite simple one but I have been struggled for some time to be honest)

Many thanks in front!

I will simplify my data to below example.

Two data tables: 1) actual by years , 2) forecast

Target: want to show actuals compare with last year, also with forecast in the same table. (I know the Dax to calculate the variances and %, but just cannot bring the correct total here)

What I have:

	Actual
Types	Year	Amount
Red	2021	1
Blue	2021	2
Yellow	2021	3
Red	2022	3
Blue	2022	4
Yellow	2022	5

	Forecast
Types	Year	Amount
Red	2022	3
Blue	2022	3
Yellow	2022	4

What I want to achieve :

Types	2022	2021	2022 Forecast
Red	3	1	3
Blue	4	2	3
Yellow	5	3	4
Total	12	6	10

What I have now:

Types	2022	2021	2022 Forecast
Red	3	1	10
Blue	4	2	10
Yellow	5	3	10

For 2021, I used DAX sameperiodlastyear, but for 2022 Forecast, when I bring the numbers to table, it shows the sum total of 10 across the 3 colours, instead of by Types.

I tried DAX selectedvalue to match Types in 2022 Forecast and the types in 2022. It sort of worked, but wouldn't show total in the

visualisation.

Types	2022	2021	2022 Forecast
Red	3	1	3
Blud	4	2	3
Yellow	5	3	4
Total	12	6	Didn't show total

Thanks heaps for your help!!

Claire 

[AilleryO]

Hi,

The merge can merge queries with multiple columns, so it should not be a problem.

If you want to use more DAX my advice would be to create a date table, linked it to your actual and forecast tables, and then create your visual based on the date table and facts table.

Hope it helps

[00claire]

Hi,

Yes I created a date table, and it linked with Forecast table and Actuals.

But both Forecast and actuals have the year 2022. Should I change the 2022 in the Forecast file to something like FC? just to differentiate?

Thank you.

[AilleryO]

The values in Actual and Forecast are different so even in the same year (from date table) they should be in 2 columns ? I think I do not get your exact situation.

[00claire]

Hi,

I'll try the merge way and see.

And do you know if there is a way to hide a column or row in PBI but didn't affect the total?

Thank you for your help!

[AilleryO]

Sorry nope. Don't know how to hide, you can try filters (filter panel, filter on this visual) but not sure it will works...

[AilleryO]

Hi,

Maybe you can merge your table using Power Query to make it simpler to display.

Merge Actual to Forecast using your types column (see image below).

Use Left Outer.

Then expand your column with amount from Forecast by clicking on header button (see below) :

Choose your amount column and then your visual should be easy :

Let us know if it works

[00claire]

Hi,

Thank you for your prompt help!

Tried the merge way (and I changed to also merge by year), and now looks like below:

Is there a way that I can hide this column? Cause there is no forecast for 2021.

And this is my simplified example, in my data there are many other columns as well so not sure how Merge works. Is there any DAX similar to sameperiodlastyear, but can help me pick the numbers same year but in a different file (forecast file)? 

Thank you!