Forum Discussion
Simple SUM filter problem driving me crazy
Dear power friends,
I have a regular monthly CALENDAR_TABLE with the typical content
DATE
01/01/2018
02/01/2018
03/01/2018
(...)
linked to a SALES_TABLES by a date column in the same format (MM / 01/2018)
I need to sum the sales for only the last month of the selected date range. For example: if I select 01/01/2018 - 03/01/2018, I will get the sum of sales only for month 03.
I've tryed:
1) create a measure called dMaxDateCalendar = LASTDATE(CALENDAR_TABLE[date])
and
2) the following measure
CALCULATE (
SUM (SALES_TABLES [sales]);
FILTER (SALES_TABLE;
SALES_TABLES[data] = [dMaxDateCalendar])
)
But it returned de sum of all date range
than I've tryed:
3) SUMX(FILTER(SALES_TABLE;
SALES_TABLE[date]=[dMaxDataCalendario]);
SALES_TABLES [sales])
but it returned, again, de sum of all date range.
What am I doing wrong?
Thanks,
Anonymous,
You may use Variables in DAX.
Measure = VAR d = LASTDATE ( CALENDAR_TABLE[date] ) RETURN CALCULATE ( SUM ( SALES_TABLE[sales] ), FILTER ( SALES_TABLE, SALES_TABLE[date] = d ) )
2 Replies
- v-chuncz-msft
Community Support
Anonymous,
You may use Variables in DAX.
Measure = VAR d = LASTDATE ( CALENDAR_TABLE[date] ) RETURN CALCULATE ( SUM ( SALES_TABLE[sales] ), FILTER ( SALES_TABLE, SALES_TABLE[date] = d ) )- AnonymousNot applicable
You're right, v-chuncz-msft.
Thanks for you time, Sam.