find values at max and min date in matrix

[zaka88]

hi

i have 2 tables in pbi data model

table 1

id

group 1

group 2

date

table 2

id

value

one-to-one relation by id, so i can asume it is one table :

table 1

id

group 1

group 2

date

value

and i have matrix visual where i want to find value at min date(min date value) and at max date(max date value):

group 1	group 2	date	value	min date value	max date value
1	33	01.01.2020	100	100	400
1	33	02.01.2020	200	100	400
1	33	03.01.2020	300	100	400
1	33	04.01.2020	400	100	400
2	44	01.01.2020	350	350	333
2	44	02.01.2020	555	350	333
2	44	03.01.2020	666	350	333
2	44	04.01.2020	333	350	333
2	77	03.01.2020	160	160	280
2	77	04.01.2020	280	160	280

also i have date filter. for example if user selects just one 2 dates in filter (3-4.01.2020) the measure should be recalculated and display next result:

group 1	group 2	date	value	min date value	max date value
1	33	03.01.2020	300	300	400
1	33	04.01.2020	400	300	400
2	44	03.01.2020	666	666	333
2	44	04.01.2020	333	666	333
2	77	03.01.2020	160	160	280
2	77	04.01.2020	280	160	280

it is should be the matrix viisual, where in Rows we have:

table 1[group 1],table 1[group 2],table 1[date]

and in Values:

table 2[value] and new measures for min\max values

example of data:

table 1
id	group 1	group 2	date
1	1	33	01.01.2020
2	1	33	02.01.2020
3	1	33	03.01.2020
4	1	33	04.01.2020
5	2	44	01.01.2020
6	2	44	02.01.2020
7	2	44	03.01.2020
8	2	44	04.01.2020
9	2	77	03.01.2020
10	2	77	04.01.2020

table 2
id	value
1	100
2	200
3	300
4	400
5	350
6	555
7	666
8	333
9	160
10	280

need help with how to create measure for that. thanks

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[zaka88]

yeah, thats cool, thanks.

but if you select just few dates in  filter it shows you wrong result.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[zaka88]

that's exactly what i need.
thank you!

[Ashish_Mathur]

You are welcome.

[ryan_mayu]

@zaka88 

I noticed that your value and date are in the separate table. How can you identify which value for which date?

[zaka88]

thanks for this question.
actually we have relation one-to-one by ID between this 2 tables. but i changed task description to clarify that.

[ryan_mayu]

@zaka88 

is below your matrix visual or table?

group 1	group 2	date	value	min date value	max date value
1	33	01.01.2020	100	100	400
1	33	02.01.2020	200	100	400
1	33	03.01.2020	300	100	400
1	33	04.01.2020	400	100	400
2	44	01.01.2020	350	350	333
2	44	02.01.2020	555	350	333
2	44	03.01.2020	666	350	333
2	44	04.01.2020	333	350	333
2	77	03.01.2020	160	160	280
2	77	04.01.2020	280	160	280

if it's a matrix visual, could you please provide the sample data for both table?

[zaka88]

updated

[ryan_mayu]

@zaka88 

please can try this

min date value = 
VAR _mindate=CALCULATE(min('Table 1'[date]),ALLEXCEPT('Table 1','Table 1'[group 2]))
return maxx(FILTER(all('Table 1'),'Table 1'[date]=_mindate&&'Table 1'[group 2]=max('Table 1'[group 2])),RELATED('Table 2'[value]))

max date value = 
VAR _maxdate=CALCULATE(max('Table 1'[date]),ALLEXCEPT('Table 1','Table 1'[group 2]))
return maxx(FILTER(all('Table 1'),'Table 1'[date]=_maxdate&&'Table 1'[group 2]=max('Table 1'[group 2])),RELATED('Table 2'[value]))

please see the attachment below.

[zaka88]

thanks, it works without date filter.

if i want select just few dates - it displays wrong result.