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.