Forum Discussion
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 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
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 ) )
4 Replies
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,
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?
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 ) )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.
