The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Hi,
I have created the data in the below format. Monthyear is a custom calculation created in power bi.
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:
How can i achieve this Output:
Thanks!!
hi @vaasubabu,
We would like to confirm if you've successfully resolved this issue or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth
Hi @vaasubabu,
We would like to follow up to see whether our super users answer resolved your issue? Please let us know if you need any further assistance.
@Ashish_Mathur, @danextian & @FBergamaschi, Thanks for your prompt response.
Thanks,
Prashanth Are
MS Fabric community support
Hi @vaasubabu ,
In the matrix visual, when using a full Date table, dates with no corresponding sales records result in blank values for measures like Revenue or MoM Difference. To avoid displaying these blanks, which negatively affects user experience — you can replace them with zeros by updating your measure as follows:
Total Revenue = COALESCE(SUM(Data[Revenue]), 0)
This ensures that even on dates with no transactions, the matrix displays 0 instead of a blank, leading to a cleaner and more consistent report.
Thanks,
Prashanth Are
MS Fabric community support
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.
Create a one-to-many single direction relationshipo from dates to fact.
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
Please see the attached pbix
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.
I do not get what the problem is, please help me understand it
You show this Output to achieve
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.
Hi,
Share the download link of the PBI file.
User | Count |
---|---|
81 | |
78 | |
37 | |
34 | |
31 |
User | Count |
---|---|
93 | |
81 | |
60 | |
49 | |
49 |