Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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's avatar
    v-chuncz-msft
    Icon for Community Support rankCommunity 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 )
        )