Dynamic columns/measures in matrix

[cheryl0316]

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 display

Total Revenue for Week 53 of 2024

Total Revenue for Week 53 of 2023

The difference between the two values

"Last Month" group should display

Total Revenue for Dec 2024

Total Revenue for Dec 2023

The difference between the two values

"Last Quarter" group should display

Total Revenue for Q4 2024

Total Revenue for Q4 2023

The difference between the two values

Thanks in advance:)

https://drive.google.com/file/d/1lG2H9oSTAxpzqK-Wv1LXYuzc8MGGzaBS/view?usp=drive_link

[v-venuppu]

Hi @cheryl0316 ,

Thank you @DataNinja777 @danextian for the Prompt Response.

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[danextian · Accepted solution]

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]

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,

[cheryl0316]

Hello Thank you for your reply. I have two questions

https://drive.google.com/file/d/1psm1YsZ1H3WfD0TKi2rmjhjQ-V_myrNZ/view?usp=sharing

1. Why is a disconnected table needed? Can I just create a slicer based on date column?

2. I've created a calculation group but the matrix can't show the results. Maybe I get it wrong?