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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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