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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
vaasubabu
New Member

Conditional formatting using difference from previous month in matrix chart

Hi,

I have created the data in the below format. Monthyear is a custom calculation created in power bi.

vaasubabu_1-1752899950792.png

Requirement is to create a matrix chart and sort the Monthyearin desc order and also conditional format if the difference >0 in green, <0 red and =0 as black

 

For Sort order I created a measure and followed the below step but the sort is not working

SortOrder = YEAR(Table[Date]) * 100 + MONTH(Table[Date])

selected monthyear -->column tools -->sort by column -->choose sortorder

 

Prev month difference: I have created a measure with this below logic:

Previous month =
VAR CurrentYearcount = SUM(table[sales])
VAR PreviousYearCount =
CALCULATE(SUM(table[sales]),
                 DATEADD(table[Date],-1, MONTH))
RETURN
CurrentYearCount - PreviousYearCount

 

How can i achieve this Output:

vaasubabu_2-1752900194662.png

 

Thanks!!

4 REPLIES 4
danextian
Super User
Super User

Hi @vaasubabu 

Createa  dedicated dates/calendar table and not rely on the dates in your fact table. Mark  it as dates table. This will help simplify time intelligence calculations.

danextian_0-1752915723536.png

Create a one-to-many single direction relationshipo from dates to fact.

danextian_1-1752915950205.png

 

 

For the reverse year month sort, you can simply get the difference between the min date in the table vs the current row date in months. The calculated column below will return -1 for the earliest month follow by -2 for the month after. 

Reverse Year Month Sort = 
VAR _MinDates =
    MIN ( 'Dates'[Date] )
RETURN
    DATEDIFF ( 'Dates'[Date], _MinDates, MONTH ) - 1 

These are sample month-related time intelligence calculations

Revenue Previous Month = 
CALCULATE ( [Total Revenue], PREVIOUSMONTH ( Dates[Date] ) )
MoM Difference = 
[Total Revenue] - [Revenue Previous Month]

 Conditionally format MoM difference as below

danextian_3-1752916195990.png

Please see the attached pbix

 





Dane Belarmino | Microsoft MVP | Proud to be a Super User!

Did I answer your question? Mark my post as a solution!


"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Thanks! Wouldn’t this approach be ineffective without joining the date table? Can we achieve it from sales table only. 
Ex: If there are no sales transactions for certain dates within the selected week, those dates won’t appear in the sales table. As a result, when we choose a date from the date table, there will be no corresponding records in the sales table, so that date will display as blank or empty which is not a good customer experience.

FBergamaschi
Solution Specialist
Solution Specialist

I do not get what the problem is, please help me understand it

 

You show this Output to achieve

FBergamaschi_0-1752909107348.png

 

but here the months is not sorted in descending order

 

So can you specify what do you have now as result and what do you want to get?

 

Thanks

It appears the sort order isn’t working correctly. Here’s how the output should look:
For January 2025, sales are 50. Then for February 2025, sales drop to 15. This decrease should be highlighted in red.
For February 2025, sales are 15. Then for March 2025, sales rise to 25. This increase should be highlighted in green.

vasubabu_0-1753112228001.png

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.