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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
PeterStuhr
Helper V
Helper V

Matrix: Combine sales per month columns and measures

Hi all,

 

Imagine I have a table like this:

 

Company NameInvoice DatePrice
A01-01-2019100
A01-02-2019130
A01-03-2019140
A01-04-2019120
A01-05-2019130
A01-06-2019110
A01-07-2019100
A01-08-2019140
A01-09-2019520
A01-10-2019200
A01-11-2019120
A01-12-2019345
A01-01-2020245
A01-02-2020200
   

 

 

Then I put that in a matrix like this:

 

test.png

 

Which is fine. However, I would like to - AFTER the date columns, to be able to put some measures. I would like to put a measure for: "FY Sales LY" , "Sales LTM", "Diff LY vs LTM". But I can't seem to make that work. Then it puts it under each month if I put it as values. Is it possible in any way? My output I want is something like this:

test2.JPG

Thanks!

1 ACCEPTED SOLUTION

OK, I updated it so that it handles all months, I was just getting it to work. Just requires copying and pasting a bunch of code and tweaking them for each month. Attached.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

6 REPLIES 6
Greg_Deckler
Super User
Super User

Attached a possible path to a solution.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler 

 

Thanks! I just tried it and seems to work alright - however it is made just for january right? How do I make it, if it has to take "any month" last year, and the YTD Months this year?

 

So lets say - from Jan-19 to whatever current month always?

OK, I updated it so that it handles all months, I was just getting it to work. Just requires copying and pasting a bunch of code and tweaking them for each month. Attached.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Thanks a million!

the last thing is that it sorts the months A-Z, how can I alter that? I have tried without luck

Glad it works!! Honestly, I thought that it came out way too convoluted to be of use but it was the only way I could think of to do what you wanted without trying to smash two visuals together. 

 

I added a sorting column to the data and did a "Sort By" column for Value 2 column. Updated PBIX.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

This is the most complicated measure I have ever had to be honest 😉

 

But it works! Only limitation is that it is not possible to do "sorting" by clicking on the columns. But that is just how it is!

 

Thanks a million @Greg 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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