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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors