0
votes

I am trying to get a sum of one column based on the date in another.

Count Date
100 05/01/2021
200 05/01/2021
300 05/01/2021
100 06/01/2021
200 06/01/2021
400 06/01/2021
100 07/01/2021
300 07/01/2021
500 07/01/2021

In SQL what I want is: SELECT SUM([COUNT]) WHERE [Date] = MIN([Date]) FROM [rpt_data]

I can't figure out how to do this in DAX. I tried: CALCULATE( SUM([Count]), MIN([Date]) ) But it says that I can't use MIN as a filter. I know this works: CALCULATE( SUM([Count]), rpt_data[Date] = "07/01/2021" ) But I need to know the first and last dates dynamically.

I need another with MAX[Date]) but I figure if I get one figured out I'll figure out the other.

1

1 Answers

1
votes

Currently, the engine doesn't know what do you want. We need to show which column we want to compare with a date. Example:

measure =
var __minDate = calculate( min(OrderTable[Date]))
return
CALCULATE( SUM(SalesTable[Count]), FILTER(ALL(SalesTable[Date]), SalesTable[Date] = __minDate)