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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Anonymous
Not applicable

How to repeat last values till the end of matrix?

Hi All,

I have a matrix in my power bi visual which looks like the below table:

Rpt DateJan-21Feb-21Mar-21Apr-21May-21Jun-21Jul-21
Jan-2110020025010050500300
Feb-2110015010020040050150
Mar-21 300200230100200150
Apr-21  250100200100150
May-21   100200300100
Jun-21    100200100
Jul-21     300300

 

I want to repeat the values in green fonts till the end of the matrix i.e.; till Jul-21. 

My final matrix should look like the below table:

Rpt DateJan-21Feb-21Mar-21Apr-21May-21Jun-21Jul-21
Jan-2110020025010050500300
Feb-2110015010020040050150
Mar-21100300200230100200150
Apr-21100300250100200100150
May-21100300250100200300100
Jun-21100300250100100200100
Jul-21100300250100100300300

 

Please help me in solving this using Dax.

 

Thanks in advance

1 ACCEPTED SOLUTION
Anonymous
Not applicable

HI @Anonymous,

Did these blank parts mean your table does not include correspond records?
If that is the case, you need to create an unconnected date table as the row of the matrix, then you can write a measure expression to use the current date and category to lookup corresponding or previous results.

Measure =
VAR currDate =
    MAX ( Date[Date] )
VAR currCate =
    MAX ( Table[Date] )
VAR prevDate =
    CALCULATE (
        MAX ( Date[Date] ),
        FILTER ( ALLSELECTED ( Date ), [Date] < currDate )
    )
VAR cValue =
    CALCULATE (
        SUM ( Table[Amount] ),
        FILTER ( ALLSELECTED ( Table ), [Date] = currDate )
    )
VAR pValue =
    CALCULATE (
        SUM ( Table[Amount] ),
        FILTER ( ALLSELECTED ( Table ), [Date] = prevDate )
    )
RETURN
    IF ( cValue <> BLANK (), cValue, pValue )

Regards,
Xiaoxin Sheng

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

HI @Anonymous,

Did these blank parts mean your table does not include correspond records?
If that is the case, you need to create an unconnected date table as the row of the matrix, then you can write a measure expression to use the current date and category to lookup corresponding or previous results.

Measure =
VAR currDate =
    MAX ( Date[Date] )
VAR currCate =
    MAX ( Table[Date] )
VAR prevDate =
    CALCULATE (
        MAX ( Date[Date] ),
        FILTER ( ALLSELECTED ( Date ), [Date] < currDate )
    )
VAR cValue =
    CALCULATE (
        SUM ( Table[Amount] ),
        FILTER ( ALLSELECTED ( Table ), [Date] = currDate )
    )
VAR pValue =
    CALCULATE (
        SUM ( Table[Amount] ),
        FILTER ( ALLSELECTED ( Table ), [Date] = prevDate )
    )
RETURN
    IF ( cValue <> BLANK (), cValue, pValue )

Regards,
Xiaoxin Sheng

lbendlin
Super User
Super User

Use LASTNONBLANK() or LASTNONBLANKVALUE() functions.

 

Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. 

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

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

August 2025 community update carousel

Fabric Community Update - August 2025

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