Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
Hi Rico,
Sorry for the late reply
And Thank you so much.
It helped me to create the table how I wanted.
Thanks
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
12 | |
2 | |
2 | |
1 | |
1 |
User | Count |
---|---|
20 | |
3 | |
2 | |
2 | |
2 |