Summarize / Lookup Data

[finan]

Hello everyone.

I'm starting my studies at PowerBi, and I came up with a challenge that I can not solve.

I need to summarize a table, grouping by a category, searching the first date of this category, according to the filters and with this category / date fetch the value at that time.

Example:

My table: 

STARTDATE	D_T	RANGE_DE_TX_DL	BAIN	VALUE
2017-09-10	35	0 Kbps~300Kbps	D	10,85138889
2017-09-10	-1	300Kbps~600Kbps	NULL	39,34027778
2017-09-10	27	600Kbps~1Mbps	B	81,405
2017-09-10	31	1Mbps~2Mbps	B	220,3291667
2017-09-13	14	2Mbps~4Mbps	C	20,18055556
2017-09-14	18	4Mbps~6Mbps	B	64,82083333
2017-09-14	22	6Mbps~10Mbps	B	52,144
2017-09-15	21	10Mbps~15Mbps	A	20,523
2017-09-15	79	>15Mbps	C	15,26111111
2017-09-15	11	600Kbps~1Mbps	A	359,9986111
2017-09-15	21	0 Kbps~300Kbps	A	23
2017-09-16	11	0 Kbps~300Kbps	A	628,7347222
2017-09-16	94	300Kbps~600Kbps	C	10,66944444
2017-09-16	35	600Kbps~1Mbps	B	708,8361111
2017-09-16	50	1Mbps~2Mbps	D	50

 Result, if no filter is applied (D_T = All; BAIN = All)

	MIN_DATE	MAX_DATE
	2017-09-10	2017-09-16
0 Kbps~300Kbps	10,85138889	628,7347222
300Kbps~600Kbps	39,34027778	10,66944444
600Kbps~1Mbps	81,405	708,8361111
1Mbps~2Mbps	220,3291667	50

Is it possible to do this?

Thank you.

[Thyago_Rezende · Accepted solution]

Hi,

It follows again:

For the first date in the filter context:

CALCULATE(SUM(VALUE);FIRSTDATE(STARTDATE))

Regards,

[finan]

@Thyago_Rezende Amazing...

I got it.

One more question.
Can I make a column with a% of the total value?

Example:

	2017-09-10	%
0 Kbps~300Kbps	10,85138889	3,08%
300Kbps~600Kbps	39,34027778	11,18%
600Kbps~1Mbps	81,405	23,13%
1Mbps~2Mbps	220,3291667	62,61%
	351,9258333	100%

[Thyago_Rezende]

Good!!

you can use quick measures for get % of total.

https://docs.microsoft.com/en-us/power-bi/desktop-quick-measures

Best regards,

[finan]

@Thyago_Rezende 

I did, I changed the field display as % of grand total.

Thanks again.

[Thyago_Rezende]

Hi @finan,

Try to do FIRSTDATE function.

https://msdn.microsoft.com/en-us/library/ee634806.aspx

It works with filter context. 

I hope help you.

Best regards,