Forum Discussion
Post ProdigyYearly 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 | 01.2020 | 32 |
| c | 01.2020 | 31 |
| v | 01.2020 | 34 |
| a | 02.2020 | 35 |
| c | 02.2020 | 36 |
| v | 02.2020 | 43 |
| a | 03.2020 | 44 |
| c | 03.2020 | 41 |
| v | 03.2020 | 46 |
| a | 04.2020 | 55 |
| c | 04.2020 | 54 |
| v | 04.2020 | 53 |
| a | 05.2020 | 65 |
| c | 05.2020 | 66 |
| v | 05.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
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)))Pbix attached.
9 Replies
- DataZoe
Microsoft 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
)
IFHi ,
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
)
- amitchandak
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
- IF
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
- v-diye-msft
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)))Pbix attached.
- IF
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,
- IF
Hi,
What I wrote an hour ago was for another post. Sorry for the mistake!
it is perfectly working.
All the best
- Greg_Deckler
Community Champion
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.- IF
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
