Forum Discussion
First and Last date
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 |
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
1 Reply
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