Modify columns in matrix based on selected data in slicer

[sreekanths]

Hi,

I have a slicer with year and month information as yyyymm. 

Based on the year-month selection in the slicer, the matrix visual has to display data from chosen month to the start of the year.

Sample Scenario

Slicer Values - 201912 to 201801

Selected Value - 201904

Expected columns in matrix

201904 201903 201902 201901

	201904	201903	201902	201901
ABC	a1	a5	a6	a11
XYZ	a2	a20	a12	a22
DEF	a3	a31	a30	a38

Creating a measure using SELECTEDVALUE isnt working as i dont want any aggregation of data.

Please suggest a solution.

[v-lili6-msft]

hi  @sreekanths 

You just drag year-month field into "Columns" of the matrix visual and then drag it into a number range slicer.

https://docs.microsoft.com/en-us/power-bi/desktop-slicer-numeric-range

https://docs.microsoft.com/en-us/power-bi/visuals/desktop-matrix-visual

Regards,

Lin

[parry2k · Accepted solution]

@sreekanths assuming this slicer table is not connected with your actual data table. Try the following measure, tweak it as per your need.

Measure = 
VAR __maxDate = MAX ( SlicerTable[Date] ) //it will give the max date on the selected year/month
VAR __selectedYear = YEAR ( __maxDate )
RETURN
CALCULATE (
SUM ( DataTable[Column] ),
YEAR ( DataTable[DateColumn] ) = __selectedYear,
DataTable[DateColumn] <= __maxDate
)

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!