Compare Actual vs Budget Monthly

[Anonymous]

Hello,

I have two tables, the first one comes from an SQL query and is a sales report  (one row per sale), and the other one, comes from an Excel, and contains the budget. Here is an example:

Sales report                                                                            

Date                        Account                   Amount                                

01/01/2019            Banamex                  $25,000                                

05/01/2019            Banamex                  $15,000

07/01/2019            Santander                $8,000

08/02/2019            Banamex                  $3,000

15/02/2019            Banamex                  $11,000

22/02/2019            Santander                $12,000

27/02/2019            Santander                $5,000

Budget

Account             Month                Budget

Banamex            January              $45,000

Banamex            February            $20,000

Santander          January              $10,000

Santander          February            $16,000

The problem is that it seems that when i try to construct the following visualization, the program doesn't understand the relation beetween "Date" in Sales Report and "Month" in Budget.

Account             Month                Budget            Sales                   %

Banamex            January              $45,000          $40,000           88.9%

Banamex            February            $20,000          $14,000           70% 

Santander          January              $10,000          $8,000             80%

Santander          February            $16,000          $17,000          106.3%

Thanks in advance

[Usha_RK]

Hi there,

I am new to Power BI and have tried to create a similar actuals versus budget report for our data re: software licenses. Although the budget shows correctly based on the different products, the actuals show up as the total spend for the month of Apr23. Could you please help me out?

 My relationships look like this:

[v-lili6-msft · Accepted solution]

HI, @Anonymous 

Just try this way:

Step1:

Add a Month column for Date in Sales report table by this formula

Month = FORMAT ( [Date], "mmmm" )

Step2:

Add Dim Month table and Dim Account table.

Step3:

Create the relationship as below:

Step4:

Create a measure for %

% = DIVIDE(SUM('Sales report'[Amount]),SUM(Budget[Budget]))

Step5:

Darg Dim field from dim table, Amount and Budget from data table and measure into a visual

Result:

here is pbix file, please try it.

Best Regards,

Lin

[Anonymous]

Thank you so much, this solved my problem!

[calerof]

Hi @Anonymous ,

Make sure you have a date table.

Then in your excel file add a column with date, and in each row write the begining of each month in the format dd/mm/yy. Use this column to establish the relationship between the date table.

In your excel file you can create a new column like the following:

Month = FORMAT( [Date], "MMM")

You should be able to get your information and comparisons afterwards.

Hope it helps.

Cheers,

Fernando