Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
onedayover
Helper II
Helper II

Matrix for month over month data and the changes

I'm trying to create a this matrix from a table that contains the current month, previous month and the change in value and percentage.

My current data table looks like this...

YearMonthMonthNameProductTotalCurrentMonthPreviousMonth
202112DecemberAPPLES688NOYES
202201JanuaryAPPLES856YESNO
202112DecemberORANGES5NOYES
202112DecemberPEARS75NOYES
202201JanuaryPEARS88YESNO
202112DecemberKIWIFRUIT35NOYES
202201JanuaryKIWIFRUIT40YESNO
202112DecemberBANANAS72NOYES
202201JanuaryBANANAS27YESNO
202112DecemberSTRAWBERRIES113NOYES
202201JanuarySTRAWBERRIES87YESNO
202112DecemberBLUEBERRIES36NOYES
202201JanuaryBLUEBERRIES38YESNO
202112DecemberWATERMELON7NOYES
202201JanuaryWATERMELON7YESNO
202112DecemberPINEAPPLE13NOYES
202112DecemberPINEAPPLE10YESNO
202112DecemberCHERRIES1NOYES
202112DecemberROCKMELON16NOYES
202112DecemberGRAPES2NOYES


This is how I would like the matrix to look...

ProductThis MonthLast MonthChangeChange %
APPLES85668816824.4%
ORANGES05-5-100.0%
PEARS88751317.3%
KIWIFRUIT4035514.3%
BANANAS2772-45-62.5%
STRAWBERRIES87113-26-23.0%
BLUEBERRIES383625.6%
WATERMELON7700.0%
PINEAPPLE1013-3-23.1%
CHERRIES01-1-100.0%
ROCKMELON016-16-100.0%
GRAPES02-2-100.0%


I've tried so many ways to do this but I just can't make it work.

Can someone please help me with how to do this?

Many thanks

darls15

 

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

I would recommend you lose the "Current Month"  and "Previous Month"  columns - you don't want to box yourself in with that.  Either use measures to figure out the max month, or use a slicer.

 

See attached for a sample implementation.

View solution in original post

2 REPLIES 2
onedayover
Helper II
Helper II

Thank you lbendlin, I appreciate your advice re current/previous months. This is exactly what I needed and it's very much appreciated 🙂

lbendlin
Super User
Super User

I would recommend you lose the "Current Month"  and "Previous Month"  columns - you don't want to box yourself in with that.  Either use measures to figure out the max month, or use a slicer.

 

See attached for a sample implementation.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.