Forum Discussion
DAX SUM filtering two date columns
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]
)
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.
4 Replies
- amitchandak
Super User
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)) - 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.
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.
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
