cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Post Prodigy

## Yearly Matrix - monthly values

Hi,

I have a month-year selection with a slicer. It includes last 36 months of data. Based on the selection of month-year in the slicer, I want to show the values for that year in a matrix.

For example; if I select 06.2020 the table should show data for 2020 for each month. Or if I select 01.2019 the table should show data for that year for each month.

I have the data below if it can be useful.

Column1Column2Column3

 a 10.2019 12 c 10.2019 13 a 10.2019 14 a 11.2019 15 c 11.2019 17 v 11.2019 23 a 12.2019 25 c 12.2019 22 v 12.2019 33 a 1.202 32 c 1.202 31 v 1.202 34 a 2.202 35 c 2.202 36 v 2.202 43 a 3.202 44 c 3.202 41 v 3.202 46 a 4.202 55 c 4.202 54 v 4.202 53 a 5.202 65 c 5.202 66 v 5.202 62

Column1

 12.2018 1.2019 2.2019 3.2019 4.2019 5.2019 6.2019 7.2019 8.2019 9.2019 10.2019 11.2019 12.2019 1.202 2.202 3.202 4.202 5.202 6.202 7.202 8.202 9.202 10.202

All the best

1 ACCEPTED SOLUTION
Community Support

Hi @IF

Plz let me know if you'd like to get below results:

``````Measure 3 = var a = SELECTEDVALUE(Table2[Column1])
return
CALCULATE(MAX(Table1[Column3]),FILTER(Table1,YEAR([Column2])=YEAR(a)))``````

Pbix attached.

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
9 REPLIES 9
Community Support

Hi @IF

Plz let me know if you'd like to get below results:

``````Measure 3 = var a = SELECTEDVALUE(Table2[Column1])
return
CALCULATE(MAX(Table1[Column3]),FILTER(Table1,YEAR([Column2])=YEAR(a)))``````

Pbix attached.

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Post Prodigy

Hi,

What I wrote an hour ago was for another post. Sorry for the mistake!

it is perfectly working.

All the best

Post Prodigy

Hi,

I am sorry that the link didn't work. Here is another link. https://gofile.io/d/cuPNYz

I hope it works this time. Actually what I want is different. I tried in two ways. The first file screen shots. If Slider1 or Slider2 is not selected, I want to see 66 value in a measure (for month 07.2020).

I tried with another way at night. The link has second file.

I saw a similar example with another project. It was not in a similar way, but mine doesn't work.

If there is a better way of uploading the files, I can do it in that way. I think it is quite easy if you open the files.

All the best,

Super User

@IF , the best way to happen for this have disconnected month-year/date table with year , qtr etc

Then use slicer on that table assume -date

And have measure like

measure =
var _max = maxx(allselected(Date), Date[Year])
return
calculate([measure], filter(Date, Date[Year] =_max))

This is another interesting way if it can work for you -https://www.youtube.com/watch?v=duMSovyosXE

Post Prodigy

Hi,

Thanks for the answer and link. It is little bit complicated to make the changes. I tried the measure but it gives error. I highlight the problematic part for me. 🙂

var _max = maxx(allselected(Date), Date[Year])
return
calculate([measure], filter(Date, Date[Year] =_max))

I also give you the link for the  file if you could share later on with the update. https://gofile.io/d/EiZDOD

All the best

Microsoft

@IF Hi! I am wondering if it may be easier just to create a column that would give you the year to use in your slicer?

A calculated column such as : Year = right(Table[Column1]),4)

Otherwise you could use this measure:

All Year Measure =
VAR _selecteddate = SELECTEDVALUE ( Table[Column1] )
VAR _selectedyear = RIGHT ( _selecteddate, 4 )
RETURN
CALCULATE (
[Measure],
REMOVEFILTERS ( Table[Column1] ),
RIGHT ( Table[Column1], 4 ) = _selectedyear
)

Respectfully,
DataZoe

See my reports and blog at https://www.datazoepowerbi.com/

Post Prodigy

Hi ,

I can use another column. However, How should I know that which year is selected?

On the other hand, when I use the measure it give error with [Measure] part. Do I do smthg wrong? When it done, should I put it to the column part in the field section?

All Year Measure =
VAR _selecteddate = SELECTEDVALUE ( Table[Column1] )
VAR _selectedyear = RIGHT ( _selecteddate, 4 )
RETURN
CALCULATE (
[Measure],
REMOVEFILTERS ( Table[Column1] ),
RIGHT ( Table[Column1], 4 ) = _selectedyear
)

Super User

@IF Sorry, having trouble following, can you post sample data as text and expected output?

But maybe a Complex Selector? https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Complex-Selector/m-p/1116633#M534

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Post Prodigy

Hi,

I uploaded file to the: https://gofile.io/d/EiZDOD  upl

I try to check if there is something posted earlier. Sometimes it is not easy to understand from similar scenarios and draw conclusion how to use it, at least for the beginners, I believe.

The table that I provided, can be selected and paste into the power bi. I don't know if it is really require to use table tool in the editing part. If I do it in that way. I have to enter the data manually. What I do, I copy the file from the power bi and paste it to the posting text. I checked if another person can copy and paste back to the power bi and it works.

I hope my explanation is understandable.

All the best