Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
radbook
Frequent Visitor

Connect actuals and plan with different data freeze dates

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)

radbook_0-1650570572946.png

 

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:

radbook_1-1650570572947.png

 

 

 

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"

 

radbook_2-1650570572950.png

 

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

radbook_3-1650570572953.png

 

 

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

 

radbook_4-1650570572956.png

 

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 🙂

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

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]))

vyangliumsft_0-1650867060575.png

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.

 

vyangliumsft_1-1650867060577.png

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

vyangliumsft_2-1650867060582.png

 

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

View solution in original post

2 REPLIES 2
radbook
Frequent Visitor

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

v-yangliu-msft
Community Support
Community Support

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]))

vyangliumsft_0-1650867060575.png

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.

 

vyangliumsft_1-1650867060577.png

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

vyangliumsft_2-1650867060582.png

 

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.