How to repeat last values till the end of matrix?

[Anonymous]

Hi All,

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

Rpt Date	Jan-21	Feb-21	Mar-21	Apr-21	May-21	Jun-21	Jul-21
Jan-21	100	200	250	100	50	500	300
Feb-21	100	150	100	200	400	50	150
Mar-21		300	200	230	100	200	150
Apr-21			250	100	200	100	150
May-21				100	200	300	100
Jun-21					100	200	100
Jul-21						300	300

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 Date	Jan-21	Feb-21	Mar-21	Apr-21	May-21	Jun-21	Jul-21
Jan-21	100	200	250	100	50	500	300
Feb-21	100	150	100	200	400	50	150
Mar-21	100	300	200	230	100	200	150
Apr-21	100	300	250	100	200	100	150
May-21	100	300	250	100	200	300	100
Jun-21	100	300	250	100	100	200	100
Jul-21	100	300	250	100	100	300	300

Please help me in solving this using Dax.

Thanks in advance

[v-shex-msft · Accepted solution]

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]

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.