Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
IF
Post Prodigy
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.

 

MonthYear.jpg

 

I have the data below if it can be useful.

Column1Column2Column3

a10.201912
c10.201913
a10.201914
a11.201915
c11.201917
v11.201923
a12.201925
c12.201922
v12.201933
a01.202032
c01.202031
v01.202034
a02.202035
c02.202036
v02.202043
a03.202044
c03.202041
v03.202046
a04.202055
c04.202054
v04.202053
a05.202065
c05.202066
v05.2020

62

 

Column1

12.2018
01.2019
02.2019
03.2019
04.2019
05.2019
06.2019
07.2019
08.2019
09.2019
10.2019
11.2019
12.2019
01.2020
02.2020
03.2020
04.2020
05.2020
06.2020
07.2020
08.2020
09.2020
10.2020

 

Thanks in advance!

All the best

1 ACCEPTED SOLUTION
v-diye-msft
Community Support
Community Support

Hi @IF 

 

The file is not exist in your provided link. 

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)))

2.gif

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.

View solution in original post

9 REPLIES 9
v-diye-msft
Community Support
Community Support

Hi @IF 

 

The file is not exist in your provided link. 

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)))

2.gif

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.

Hi,

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

it is perfectly working.

All the best

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).

1.jpg2.jpg

 

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

 

3.jpg4.jpg

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,

amitchandak
Super User
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

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

DataZoe
Employee
Employee

@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,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

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
)

 

Greg_Deckler
Super User
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

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.