First and Last date

[slyfox]

Dear Community,

please help to figure out why my expression not working as expected
Sum of Product = CALCULATE(SUM(Table[Product]),Date=OR(FIRSTDATE(Table[Date]),LASTDATE(Table[Date]))

formula keep to return all sum values in a selected date range.

Source
Date	Product	Qty
01-02-18	A	2
01-02-18	B	5
01-02-18	B	6
03-02-18	A	7
03-02-18	A	9
03-02-18	B	2
06-02-18	A	6
06-02-18	A	7
06-02-18	B	9

And Expected result

	A	B
01-02-18	2	11
06-02-18	13	9

[v-yuta-msft · Accepted solution]

Hi slyfox ,

To achieve your requirement, you can create a measure and use DAX below:

Result = CALCULATE(AVERAGE('Table'[Qty]), FILTER('Table', ('Table'[Date] = MIN('Table'[Date]) || 'Table'[Date] = MAX('Table'[Date])) && 'Table'[Date] = EARLIER('Table'[Date]) && 'Table'[Product] = EARLIER('Table'[Product])))

PBIX here: https://www.dropbox.com/s/am1wnov5mng3u4i/First%20and%20Last%20date.pbix?dl=0

Regards,

Jimmy Tao