Forum Discussion

vaasubabu's avatar
vaasubabu
New Member
1 year ago
Solved

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. Requirement is to create a matrix chart and sort the Monthyearin desc order and also co...
Need Help
  • v-prasare's avatar
    v-prasare
    1 year ago

    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

danextian's avatar
danextian
Super User
1 year ago

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

 

vasubabu's avatar
vasubabu
Frequent Visitor
1 year ago

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.