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
hsw_1
New Member

How to fill in a matrix column with previous values

Hi

I am relatively new to Power Bi desktop and trying to figure out how to backfill a matrix visual with previous column values (ratings).  Please can anyone advise how to do this? thank you

 

My matrix looks like this

 Matrix Visual    
 03-Jan29-Jan14-Mar11-Apr24-Apr
Vendor1 A+ A- 
Vendor2  B- B+
Vendor3  A+ A-

 

and I want to fill in the missing values (in bold)

 03-Jan29-Jan14-Mar11-Apr24-Apr
Vendor1 A+A+A-A-
Vendor2  B-B-B+
Vendor3  A+A+A-

My data tables are as follows:

Tables    
 RatingVendor idRating dateRating value
  129-JanA+
  111-AprA-
  214-MarB-
  224-AprB+
  314-MarA+
  324-MarA-
     
     
VendorVendorVendor idVendor Name 
  1Vendor1 
  2Vendor2 
  3Vendor3 
3 REPLIES 3
hsw_1
New Member

Thanks for this @tamerj1   However, I couldn't get it to work for me.  It seems that because I don't have a row in the Rating table for every Vendor/RatingDate combo, then it will not assign a measure value for those cells.  I've also tried creating a calculated table in AAS with just the Ratingdate values but this doesn't seem to have helped either.   Not sure what else to do apart from a backend fix to populate the data manually - seems like it shouldn;t be this difficult though

 

@hsw_1 

Better to use a dim date table. In this case you can populate any value as the matrix would be sliced by two dim tables. 

tamerj1
Super User
Super User

@hsw_1 

Maybe I'm over complicating, not even sure it will work 

Rating Measure =
VAR CurrentDate =
CALCULATE ( SELECTEDVALUE ( Rating[Date] ), ALL ( Vendor ) )
VAR PreviousDate =
MAXX (
FILTER (
CALCULATETABLE ( ALL ( Rating[Date] ), ALL ( Vendor ) ),
Rating[Date] < CurrentDate
),
CurrentDate
)
VAR CurrentVendor =
CALCULATE ( SELECTEDVALUE ( Vendor[Vendor Name] ), ALL ( Rating ) )
RETURN
IF (
ISEMPTY ( Rating ),
CALCULATE (
MAX ( Rating[Rating Value] ),
REMOVEFILTERS (),
Vendor[Vendor Name],
CurrentVendor,
Rating[Date] = CurrentDate
),
MAX ( Rating[Rating Value] )
)

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.