Reply
tanya98
Regular Visitor

Query Resources Issue - Actual vs Budget Finance Report

I am trying to construct a matrix that displays my details as rows, presenting five years of data that includes both actual values and budget figures. Specifically, I have five years of actual data and budget values only for the current year, along with calculations for variances between current year (CY) and last year (LY) actuals, as well as the growth from CY actual to LY actual and budget performance. However, I'm facing a challenge because I lack a dedicated column for these particulars; instead, the data is sourced from various MIS columns across three different tables, with inconsistent naming conventions. I can create the visual representation, but it breaks when I attempt to filter the months using a slicer.

2 REPLIES 2
tanya98
Regular Visitor

sample2.PNG

Hi @Poojara_D12 , 

 

The requirement is not have year slicer. We will only have the month slicers along with the territory. Also, there are some MIS types which I have to use as rows in the matrix. All of them are in different table and with different names. The matrix should look like the image attached where particulars are not coming directly as a column name and in the column we display 5 years Actual values and CY growth and variance

Poojara_D12
Super User
Super User

Hi @tanya98 

To create your matrix and resolve the filtering issue:

  1. Consolidate Data: Use Power Query to combine data from multiple tables into a single table with standardized columns (e.g., Year, Month, Actuals, Budget).

  2. Create a Date Table: Build a Date Table with columns like Year, Month, and Month Name, and link it to your data table.

  3. Define Measures: Use DAX to calculate:

    • Current Year (CY) Actuals
    • Last Year (LY) Actuals
    • Variance (CY vs. LY)
    • Growth % (CY vs. LY)
    • Budget Performance (CY Actuals vs. Budget)
  4. Build the Matrix:

    • Use Year/Month as rows, Measure Type (Actuals/Budget) as columns, and your DAX measures as values.
  5. Fix the Slicer: Use the Date Table as the source for the slicer and ensure proper relationships between the Date Table and data table.

This setup ensures dynamic filtering and accurate calculations for your matrix.

 

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS 

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a "Kudos"

Kind Regards,
Poojara - Proud to be a Super User
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS
avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)