Actual vs Budget weekly sales

[Mepoo127]

Hi,

I am doing report to compare weekly actual sales and budget. Actual sales data come from Dynamics 365 and weekly budget comes from an excel file.

 However, the tricky part is sales come from different teams and budget is set across the team as well. So I have to compare weekly sales by Team.

I need to combine these two tables into one with the same weekend and Business Unit so that I can have a chart to compare the actual and target.

Could you please help me with this?

Thank you very much!

[v-jiascu-msft · Accepted solution]

Hi @Mepoo127.

Maybe you just need a date table like this:

Calendar =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2017, 1, 1 ), DATE ( 2017, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month", MONTH ( [Date] ),
    "Week", WEEKNUM ( [Date], 2 )
)

Then you establish relationships with budget and sales. The report could be like this:

'Calendar'[Week]   Sales   Budget  Compare

If you want more detailed help, please provide a dummy sample.

Best Regards,

Dale

[Mepoo127 · Accepted solution]

Hi Dale,

Again, thank you very much for your advice and suggestion. I successfully did it.

I created 2 Week Ending tables extracted from Actual Sales and Target Sales, as well as 2 Business Unit tables from those two Sales tables. Then I managed the relationship among those tables and it's done 🙂

Below is the screenshot of the relationship diagram.

Again, thank you very much Dale!!

[v-jiascu-msft · Accepted solution]

Hi @Mepoo127.

Maybe you just need a date table like this:

Calendar =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2017, 1, 1 ), DATE ( 2017, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month", MONTH ( [Date] ),
    "Week", WEEKNUM ( [Date], 2 )
)

Then you establish relationships with budget and sales. The report could be like this:

'Calendar'[Week]   Sales   Budget  Compare

If you want more detailed help, please provide a dummy sample.

Best Regards,

Dale

[Mepoo127 · Accepted solution]

Hi Dale,

Again, thank you very much for your advice and suggestion. I successfully did it.

I created 2 Week Ending tables extracted from Actual Sales and Target Sales, as well as 2 Business Unit tables from those two Sales tables. Then I managed the relationship among those tables and it's done 🙂

Below is the screenshot of the relationship diagram.

Again, thank you very much Dale!!

[v-jiascu-msft · Accepted solution]

My pleasure. Why using two weekend tables? One more suggestion here.

Weekend =
FILTER (
    DISTINCT (
        UNION ( VALUES ( 'Actual Sales'[weekend] ), VALUES ( 'Target Sales'[weekend] ) )
    ),
    ISBLANK ( [weekend] ) = FALSE ()
)

Best Regards,

Dale

[Mepoo127]

Your suggestion works perfectly Dale 🙂  Can't be any happier!!!

Thank you very much for your help with this!

[Mepoo127]

Hi Dale,

One Weekend table is from the Actual sales, and the other is the Weekend from Target Sales. Then I connect them together.

I will try your suggestion with the Union formula.

Thank you very much!

Best Regards,

Mimi

[Mepoo127]

Hi Dale,

Thank you so very much for your reply.

I will try what you suggest and get back to you if I have any other problems.

Kind Regards,

Mimi