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
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!:
The Definitive Guide to Power Query (M)

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!:
The Definitive Guide to Power Query (M)

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!:
The Definitive Guide to Power Query (M)

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!:
The Definitive Guide to Power Query (M)

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 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