Budget vs Actual by cumulative by !Q, 2Q, ,1H, 3Q, 4Q, Full Year

[Grace1]

Hi 

I have below sample reports budget and actual by mth and requrie to output report by quarter.

Appreciate any advice. Thanks.BudgetActualReport

[Icey · Accepted solution]

Hi @Grace1 ,

Please check if this could meet your requirement:

1. Connect to Excel file.

In Power Query Editor,

2. Transform data of both Actual and Budget tables:

1.  Remove button 1 row (total row).
2.  Remove column "Total".
3.  Select "Column1" and "Type" columns, then unpivot other columns.
4.  Rename columns.

3. Merge queries as new.

4. Expand table.

5. Add conditional column.

6. Filter rows.

7. Remove column "Month.1" and "Month eq Month.1".

8. Rename column "Budget.1" with "Budget".

9. Add conditional column.

10. Change type of "MonthNum" column.

11. Close and Apply.

In Power BI Desktop,

12. Create a Calendar table.

Calendar = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2019, 1, 1 ), DATE ( 2019, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month", MONTH ( [Date] ),
    "Quarter", QUARTER ( [Date] )
)

13. Create relationship.

14. Create columns in Calendar table.

Q = SWITCH ( 'Calendar'[Quarter], 1, "1Q", 2, "2Q", 3, "3Q", 4, "4Q" )

Half Year = SWITCH('Calendar'[Quarter],1,"1H",2,"1H",3,"2H",4,"2H")

15. Create a Measure.

Banlance = SUM('Actual & Budget'[Actual])-SUM('Actual & Budget'[Budget])

16. Create a Matrix visual.

For more details, please check the attached PBIX file.

Best Regards,

Icey

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

[Grace1 · Accepted solution]

Thanks. 

[Icey · Accepted solution]

Hi @Grace1 ,

Please check if this could meet your requirement:

1. Connect to Excel file.

In Power Query Editor,

2. Transform data of both Actual and Budget tables:

1.  Remove button 1 row (total row).
2.  Remove column "Total".
3.  Select "Column1" and "Type" columns, then unpivot other columns.
4.  Rename columns.

3. Merge queries as new.

4. Expand table.

5. Add conditional column.

6. Filter rows.

7. Remove column "Month.1" and "Month eq Month.1".

8. Rename column "Budget.1" with "Budget".

9. Add conditional column.

10. Change type of "MonthNum" column.

11. Close and Apply.

In Power BI Desktop,

12. Create a Calendar table.

Calendar = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2019, 1, 1 ), DATE ( 2019, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month", MONTH ( [Date] ),
    "Quarter", QUARTER ( [Date] )
)

13. Create relationship.

14. Create columns in Calendar table.

Q = SWITCH ( 'Calendar'[Quarter], 1, "1Q", 2, "2Q", 3, "3Q", 4, "4Q" )

Half Year = SWITCH('Calendar'[Quarter],1,"1H",2,"1H",3,"2H",4,"2H")

15. Create a Measure.

Banlance = SUM('Actual & Budget'[Actual])-SUM('Actual & Budget'[Budget])

16. Create a Matrix visual.

For more details, please check the attached PBIX file.

Best Regards,

Icey

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

[Grace1]

Hi Icey

Can i have the source file : Budget vs Actual.xlsx ?

Thanks

[Icey]

Hi @Grace1 ,

The data is from your screenshots. Please check the attached .xlsx file.

Best Regards,

Icey

[Grace1 · Accepted solution]

Thanks. 

[Grace1]

Hi Icey

I have try and it works from the data from my screen shot.
But when I try for another  type of expense, the Month appear in alphabetic order  both from Budget and Actual.  
(ie. Apr, Aug...etc)

But I need to appear as  Apr, May, Jun....etc

Attach are 2 pic for your viewing.

Thanks for your help. 

[Icey · Accepted solution]

Hi @Grace1 ,

Try to create a [MonthNum] column and then sort your [Month] column by it.

Reference: 

Sort By Month Names In Power BI;

Power BI Tips: Sort by Month Name.

Best Regards,

Icey

[Grace1]

Dear Icey

I don't know why different asset codes have the same budget and actual values. Should have different values.

Appreciate advice.

Thank & Regard

Grace

[Grace1]

Dear Icey

Please ignore the above issue.  I found the cause of the error.

Thank & Regard

Grace 

[Grace1]

Got it, thank you Icey for your quick feedback.

Appreciate your help.

[amitchandak]

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Refer

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

https://radacad.com/pivot-and-unpivot-with-power-bi

Appreciate your Kudos.

[Ashish_Mathur]

Hi,

By balance do you mean variance?  Try this

1. In the Query Editor, create a column in the Budget file with the word Budget in each cell.  Name this column as Budget_Actual
2. In the Query Editor, create a column in the Actual file with the word Actual in each cell.  Name this column as Budget_Actual
3. Follow the steps shown in this video to append data from multiple worksheets
4. Unpivot the appended dataset to create just one column with all months and another with all values.  Rename the Attribute column to Date
   1. Ensure that the entries in the Attribute column are actual date entries.  You may right click on the column and Change type to Date
5. Create a Calendar Table and build a relationship between the Date column of the appended dataset to the Date column of the Calendar Table
6. Create calcualted columns in the Calendar Table to extract Year, Month Name, Month number and Quarter
7. To a matrix visual, drag Quarter and Month name from the Calendar Table to the column label and Budget_Actual to the row label
8. Write this measure

Total = SUM(Data[Amount])