Connect actuals and plan with different data freeze dates

[radbook]

Dear community,

I am trying to set up a report that compares Actuals vs Plan but I struggle a lot how to set it up correctly - I would find some solution to manipulate my source excel, however I want to have a good solution in PBI instead.

My setup:

Actuals table, updated once per week (at "data freeze date")

Data freeze date	Month (is for whole month, I use first of month as reference date)	Orders
20.04.2022	01.04.2022	21
20.04.2022	01.05.2022	13
12.04.2022	01.04.2022	18
12.04.2022	01.05.2022	10
….
19.04.2021	01.04.2021	24
19.04.2021	01.05.2021	20

Plan table, updated once per month, as our plan gets an update every month (at "plan freeze date")

Plan freeze date (always first of month)	Month (plan is for whole month, I use first of month as reference date)	Planned Orders
01.04.2022	01.04.2022	25
01.04.2022	01.05.2022	24
…
01.04.2021	01.04.2021	25
01.04.2021	01.05.2021	28

My first request: compare the Actuals status of this year vs previous year via selection of calendar week:

For this I created two date tables, one for data freeze date and one for month (as they are 2 completely different time events)

Select "week of year" via slicer from "DateTableFreezeDate" which refers to column "data freeze date" from my Actuals table --> this works fine, it ignores data from all other calendar weeks:

However, when I try to connect this to my Plan data, I get in trouble.

My first idea was to use the "DateTableFreezeDate" also for my Plan freeze date and use it as a relationship. However, when selecting the calender week like in example above, no Plan data is shown as it has data freeze date only of first of the month.

Then I created a new dateTable for my PlanFreezeDate and linked this to my "DateTableFreezeDate" via "First day of month"

When I put everything together in a table it gives me the desired result:

However, when I turn the table into a bar chart it seems to ignore the freeze date and gives me much higher planned orders:

Now I have two questions:

1. Is my way how to combine the actuals and budget like this in principle a good way? I plan to use the same data model for a lot of other reports so I want to keep it clean right from the start.

1. Why does it "ignore" my calendar week selection in the bar chart?

Thanks a lot in advance 🙂

[radbook]

Thanks a lot for your detailed instructions, it works now 🙂 

[Anonymous · Accepted solution]

Hi  @radbook ,

Here are the steps you can follow：

1. Create calculated table.

Table =
CALENDAR(Min('Plan table'[Plan freeze date]),MAX('Actuals table'[Data freeze date]))

2. Create a week column for both tables.

week = WEEKNUM('Actuals table'[Data freeze date],1)

week = WEEKNUM('Plan table'[Plan freeze date],1)

3. Use Enter data to create a data table.

4. Create measure.

Measure 3 =
var _select=SELECTEDVALUE('Table'[Week])
return
SWITCH(
        TRUE(),
        MAX('Table2'[Yeargroup])="2021Freeze",CALCULATE(SUM('Actuals table'[Orders]),FILTER(ALL('Actuals table'),'Actuals table'[week]=_select&&'Actuals table'[Month Name]=MAX('Actuals table'[Month Name])&&YEAR('Actuals table'[Data freeze date])=2021)),
MAX('Table2'[Yeargroup])="2022Freeze",CALCULATE(SUM('Actuals table'[Orders]),FILTER(ALL('Actuals table'),'Actuals table'[week]=_select&&'Actuals table'[Month Name]=MAX('Actuals table'[Month Name])&&YEAR('Actuals table'[Data freeze date]=2022))),
MAX('Table2'[Yeargroup])="2021Plan",CALCULATE(SUM('Plan table'[Planned Orders]),FILTER(ALL('Plan table'),YEAR('Plan table'[Plan freeze date])=2021&&'Plan table'[week]=_select-1&&'Plan table'[Month Name]=MAX('Actuals table'[Month Name]))),
MAX('Table2'[Yeargroup])="2022Plan",CALCULATE(SUM('Plan table'[Planned Orders]),FILTER(ALL('Plan table'),YEAR('Plan table'[Plan freeze date])=2022&&'Plan table'[week]=_select-1&&'Plan table'[Month Name]=MAX('Actuals table'[Month Name])))
)

5. Result:

Place [Month Name] to Axis, [Yeargroud] to Legend, [Measure3] to Value

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly