Query Resources Issue - Actual vs Budget Finance Report

[tanya98]

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.

[tanya98]

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]

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