Sum with filter on DAX/PowerBI

Question

I need to sum each month the ID's amounts that meet the next criterias:

Date End < Agreement date

Date End < Month to show

I have tried with filter, sum and others options, but the result is the same.

My datas are:

ID  START DATE  AGREEMENT DATE  END DATE    AMOUNT

1   09/15/2018  01/01/2019      02/20/2019  100

2   09/20/2018  01/15/2019      12/01/2019  100

3   10/01/2018  03/01/2019      12/01/2019  100

4   10/01/2018  03/20/2019      05/01/2019  100

5   11/10/2018  07/10/2019                  100

6   09/15/2018  04/05/2019      05/01/2019  100

7   10/01/2018  06/10/2019      05/01/2019  100

8   10/20/2018  07/11/2019      04/10/2019  100

9   11/11/2018  08/01/2019                  100

10  11/01/2018  06/01/2019      04/10/2019  100

This is my DAX code:

Result =
CALCULATE (
    SUM ( 'Report Diario'[Amount] );
    USERELATIONSHIP ( Calendario[Date]; 'Report Diario'[End Date] );
    FILTER (
        'Report Diario';
        'Report Diario'[End Date] < 'Report Diario'[Agreement date]
    )
)

How does your Calendario look? what is the relation between the tables? — Aldert
It will be helpful if you provide an image of your data model that shows all relevant tables and their relations, and describe an example of a desired outcome. — RADO
Thanks for your responses. Calendario just have a date value. The relation is between End Date/Agreement Date and Calendario.date — Zunzunm3
Can you please show us how the end result should look like? See comment @RADO! — Aldert
The results should look like: May->300 (ID 7, 8 and 10) June-> 100 (ID 8) — Zunzunm3

Zunzunm3 Zunzunm3 · Accepted Answer · 2019-06-10T11:19:16

finally, I have created a new table and the next calculated column works:

Result = CALCULATE(SUM('Report Diario'[Amount]);FILTER('Report Diario';AND(Calendario[Date]<'Report Diario'[Agreement Date];AND(Calendario[Date]>'Report Diario'[End Date];NOT(ISBLANK('Report Diario'[End Date]))))))

Sum with filter on DAX/PowerBI

1 Answers