Forum Discussion

cheryl0316's avatar
cheryl0316
Helper II
1 year ago
Solved

Dynamic columns/measures in matrix

I’d really appreciate any help with this. I’ve created a sample dataset and would like to build a matrix as follows:       When the user selects 2024 and 2023:   "Last Week" group should...
Need Help
  • danextian's avatar
    danextian
    1 year ago

    Hi cheryl0316 

    Ensure that you have a separate date dimensions table. Creat the following measures

    Current Year = 
VAR _Year = CALCULATE ( MAX ( Dates[Year] ), ALLSELECTED ( Dates ) )
RETURN
    CALCULATE (
        SUM ( 'Table'[Revenue] ),
        KEEPFILTERS ( Dates[Year] = _Year )
    )

Previous Year = 
VAR _Year = CALCULATE ( MIN ( Dates[Year] ), ALLSELECTED ( Dates ) )
RETURN
    CALCULATE (
        SUM ( 'Table'[Revenue] ),
        KEEPFILTERS ( Dates[Year] = _Year )
    )

Difference = 
[Current Year] -  [Previous Year]

    Create these calculation items

    Last Week = 
CALCULATE(
    SELECTEDMEASURE(),
    FILTER(
        ALL('Table'[Week No]),
        'Table'[Week No] = 52
    )
)
--NOTE: no 53  in your sample data for 2023 and 2024

Last Month = 
CALCULATE(
    SELECTEDMEASURE(),
    FILTER(
        ALL ( 'Dates' ),
        Dates[Month Number] = 12
    )
)

Last Quarter = 
CALCULATE(
    SELECTEDMEASURE(),
    FILTER(
        ALL(Dates),
        Dates[Quarter] = 4
    )
)

     

DataNinja777's avatar
DataNinja777
Super User
1 year ago

Hi cheryl0316 ,

 

To build a matrix in Power BI that displays current year and prior year revenue comparisons for specific periods—Last Week, Last Month, and Last Quarter—you can use a combination of DAX measures and user-driven slicers. The goal is to dynamically calculate total revenue for a selected year and its prior year, and display both values along with their difference under each period group in the matrix visual.

Start by creating a disconnected table to allow users to select the current and prior year. This can be done with the following DAX:

YearSelector = DISTINCT(SELECTCOLUMNS('DateTable', "Year", YEAR('DateTable'[Date])))

Use two slicers based on this table: one for the selected year and one for the prior year. Next, define DAX measures to calculate the revenue for each time frame. For example, for the last week, assuming Week 53 is the relevant week:

CurrentWeekRevenue =
CALCULATE(
    SUM('RevenueTable'[Revenue]),
    'DateTable'[WeekNum] = 53,
    'DateTable'[Year] = SELECTEDVALUE('YearSelector'[Year])
)

PriorWeekRevenue =
CALCULATE(
    SUM('RevenueTable'[Revenue]),
    'DateTable'[WeekNum] = 53,
    'DateTable'[Year] = SELECTEDVALUE('YearSelector'[Year]) - 1
)

WeekRevenueDifference = [CurrentWeekRevenue] - [PriorWeekRevenue]

The same pattern can be applied for month and quarter-level calculations by modifying the filters accordingly—for instance, using 'DateTable'[Month] = 12 for December and 'DateTable'[Quarter] = 4 for Q4.

To group these measures cleanly in the matrix, consider creating a field parameter or using calculation groups. The matrix should be configured with 'Product Name' on the rows and the selected measures or parameter on the columns. This setup ensures the matrix displays the correct revenue figures and differences for each product across the defined periods, dynamically updating based on the year selections made by the user.

 

Best regards,