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.

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.

jhilsee's avatar
jhilsee
New Member
1 year ago

Thank you!  I will give this a try.