Forum Discussion

jhilsee's avatar
jhilsee
New Member
1 year ago
Solved

Help with Matrix

I am new to Power BI and have a basic understanding of how to use it.  I am trying to calculate the variance between 2024 and 2025 in my matrix visiual. My data is set up with a column for the year.  

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Thanks for the reply from Gabry.

     

    Hi jhilsee ,

     

    I used sample data, here are the exact steps:

    1. Create a date table:

     

    2. Create a relationship as shown:

     

    3. Create a measure:

    Profit Difference = 
VAR _minyear=MINX(ALLSELECTED('Date'[Year]),'Date'[Year])
VAR _maxyear=MAXX(ALLSELECTED('Date'[Year]),'Date'[Year])
return
CALCULATE(SUM('financials'[Profit]),'Date'[Year]=_maxyear)-CALCULATE(SUM('financials'[Profit]),'Date'[Year]=_minyear)

     

    4. Create a slicer using the Year field from the Date table and replace the Year column in the matrix with the Year field from the Date table. Result:

    Best Regards,
    Zhu

     

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

3 Replies

  • Gabry's avatar
    Gabry
    Super User
    1 year ago

    Hello jhilsee ,

    It depends on how you want to display it.

    The first thing you should do is create a date table.

    You can create a date table with the following:

    Dates= calendarauto()

    However, I recommend learning more about this, as a date table is very important.

     

    Then you need to set a relationship between the date from dates table and the date of Combined table.

    Then, you can create a measure like this:

     

    Variance CY vs PY= sum('COmbined'[amount])- calculate(sum('COmbined'[amount]), sameperiodlastyear('Dates'[date])

    This formula calculates the amount for the current year, based on the filter context, and subtracts the amount from the previous year.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Thanks for the reply from Gabry.

     

    Hi jhilsee ,

     

    I used sample data, here are the exact steps:

    1. Create a date table:

     

    2. Create a relationship as shown:

     

    3. Create a measure:

    Profit Difference = 
VAR _minyear=MINX(ALLSELECTED('Date'[Year]),'Date'[Year])
VAR _maxyear=MAXX(ALLSELECTED('Date'[Year]),'Date'[Year])
return
CALCULATE(SUM('financials'[Profit]),'Date'[Year]=_maxyear)-CALCULATE(SUM('financials'[Profit]),'Date'[Year]=_minyear)

     

    4. Create a slicer using the Year field from the Date table and replace the Year column in the matrix with the Year field from the Date table. Result:

    Best Regards,
    Zhu

     

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