Yearly Matrix - monthly values

[IF]

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

[v-diye-msft · Accepted solution]

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,

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

it is perfectly working.

All the best

[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,

[amitchandak]

@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

[DataZoe]

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

[IF]

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]

@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