DAX SUM filtering two date columns

[alan_ar]

How can I do this in Power BI using DAX?

SELECT 
    SUM( VALUE_FIELD ) 
FROM 
    TABLE 
WHERE 
    DATE_FIELD_1 = '2019-12-01' 
    AND 
    DATE_FIELD_2 = '2019-12-01'

2019-12-01 is a date filter selected from the CALENDAR table created in Power BI and related to DATE_FIELD_1

I've tried and it didn't work:

SUMX(
   FILTER(TABLE,
       TABLE[DATE_FIELD_2] = RELATED( CALENDAR[DATE_FIELD] )
   ),
   TABLE[VALUE_FIELD]
)

SUMX(
    FILTER(TABLE,
        TABLE[DATE_FIELD_2] <= MAX( CALENDAR[DATE_FIELD] ) &&
        TABLE[DATE_FIELD_2] >= MIN( CALENDAR[DATE_FIELD] )
    ),
    TABLE[VALUE_FIELD]
)

[Anonymous]

Hi @alan_ar 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[Anonymous · Accepted solution]

Hi @alan_ar 

I build a sample table to have a test.

Table:

Calendar Table:

CALENDAR = CALENDAR(DATE(2019,12,01),DATE(2019,12,04))

I build a relationship between Date_Field (Calendar Table)and  Date_Field 1 (Table).

You can build a slicer or add Date_Field into Filter Fileds by calendar table and build a measure.

SUM BY SELECT CALENDAR = 
VAR _Select = SELECTEDVALUE('CALENDAR'[DATE_FIELD])
Return
SUMX(FILTER('Table','Table'[DATE_FIELD_2] = _Select),'Table'[VALUE_FIELD])

Result:

You can download the pbix file from this link: DAX SUM filtering two date columns

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Ashish_Mathur]

Hi,

Try this measure

=calculate(sum(TABLE[DATE_FIELD_2]),filter(table,table[date-field_1]=date(2019,1,1)&&table[date-field_1]=table[date-field_2]))

Hope this helps.

[amitchandak]

@alan_ar if the calendar is joined with the date of your date and you are using slicer filter then

sum(TABLE[DATE_FIELD_2] )

Table is not joined with date table

measure =
var _max =maxx(allselected(date),date[date])
var _min =minx(allselected(date),date[date])

return
calculate(Sum(TABLE[DATE_FIELD_2] ), filter(Table, Table[Date] <=_max && Table[Date] >=_min))