How to repeat last values of a column till the end of the matrix?

[Anonymous]

Hi All,

I have a matrix where I need to repeat the last values of the column till the end of the matrix.

For example:

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

Report Date	Jun-21	Jul-21	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21
Jun-21	100	200	100	400	700	1000	1300
Jul-21	50	450	200	100	100	100	100
Aug-21		100	350	200	200	200	200
Sep-21			100	200	200	200	200
Oct-21				500	100	100	100
Nov-21					200	300	400
Dec-21						100	450

I want my visual to look like the below table (i.e; the bold values should repeat till Dec-21)

Report Date	Jun-21	Jul-21	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21
Jun-21	100	200	100	400	700	1000	1300
Jul-21	50	450	200	100	100	100	100
Aug-21	50	100	350	200	200	200	200
Sep-21	50	100	100	200	200	200	200
Oct-21	50	100	100	500	100	100	100
Nov-21	50	100	100	500	200	300	400
Dec-21	50	100	100	500	200	100	450

It will be a great help if someone could advise how to write a dax?

Thanks in advance

[Anonymous · Accepted solution]

Hi @Anonymous 

From your screenshot I know that there are two date columns in matrix row and column.

I suggest you to create two unrelated date table based on two date columns in your data table.

Then try this code. Report Date and Date2 table are dax table based on date columns in my data table.

Fill Last Value in Matrix = 
VAR _T1 = GENERATE(VALUES('Table'[Report Date]),VALUES('Table'[Date2]))
VAR _T2 = ADDCOLUMNS(_T1,"Value",CALCULATE(SUM('Table'[Value]),FILTER('Table',AND('Table'[Report Date] = EARLIER([Report Date]),'Table'[Date2]=EARLIER([Date2])))))
VAR _T3 = ADDCOLUMNS(_T2,"MaxDate",MAXX(FILTER(_T2,AND([Date2]=EARLIER([Date2]),[Value]<>BLANK())),[Report Date]))
VAR _T4 = ADDCOLUMNS(_T3,"NewValue",IF([Value] <>BLANK(),[Value],SUMX(FILTER(_T3,AND([Date2]=EARLIER([Date2]),[Report Date]=[MaxDate])),[Value])))
RETURN
SUMX(FILTER(_T4,AND([Report Date] = MAX('Report Date'[Report Date]),[Date2]=MAX(Date2[Date2]))),[NewValue])

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Rico,

Sorry for the late reply

And Thank you so much.
It helped me to create the table how I wanted.

Thanks