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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Grace1
Helper II
Helper II

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

Hi 

 

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

Appreciate any advice. Thanks.BudgetBudgetActualActualReportReport

 

 

 

3 ACCEPTED SOLUTIONS
Icey
Community Support
Community Support

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.

merge.PNG

 

4. Expand table.

expand.PNG

 

5. Add conditional column.

cond.PNG

 

6. Filter rows.

filter.jpg

 

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

 

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

 

9. Add conditional column.

condi.PNG

 

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.

rela.PNG

 

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.

matrix.PNG

total.PNG

total2.PNG

 

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.

View solution in original post

Icey
Community Support
Community Support

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

View solution in original post

11 REPLIES 11
Icey
Community Support
Community Support

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.

merge.PNG

 

4. Expand table.

expand.PNG

 

5. Add conditional column.

cond.PNG

 

6. Filter rows.

filter.jpg

 

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

 

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

 

9. Add conditional column.

condi.PNG

 

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.

rela.PNG

 

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.

matrix.PNG

total.PNG

total2.PNG

 

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.

Hi Icey

 

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

 

Thanks

Icey
Community Support
Community Support

Hi @Grace1 ,

 

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

 

 

Best Regards,

Icey

Thanks. 

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.IMG_20200415_114001.jpgIMG_20200415_114020.jpg 

Icey
Community Support
Community Support

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

IMG_20200522_143526.jpg

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

Dear Icey

 

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

 

Thank & Regard

Grace 

Got it, thank you Icey for your quick feedback.

 

Appreciate your help.

amitchandak
Super User
Super User

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
Super User
Super User

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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