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

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

Anonymous — Wed, 06 Oct 2021 16:28:27 GMT

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

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

Anonymous — Mon, 11 Oct 2021 06:07:17 GMT

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.

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

Anonymous — Tue, 26 Oct 2021 07:37:00 GMT

Hi Rico,

Sorry for the late reply

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

Thanks